Macro to copy data from one file to another in the same folder

Hi guys/gals,
The task of maintaining salaried time and bonus sheets fell on my lap and I made a goof in my calculation of comp days and need to revise my workbook to catch the error... Only problem is we are already on pay period 4, and I have a bunch of employees who would have to copy everything over.

I don't know the best way to transfer all their data over to the new file, and was thinking a macro on the new file that asks the user to pick the old timesheet (probably within the same file folder for simplicity) would be the best way to do it. Can anyone point me in the right direction? I'm open to suggestions if there is a better way other than said macro.

Anyway, the data range for their entries that needs to be copied is:

Please Login or Register  to view this content.

As you can see, if I continue to make errors I will need to continue the Pay Period theme above! I'm not very good at translating what I want to do into macro code that works.

This forum is awesome btw. I'm an excel novice, and I learn something new on here all the time.

Attached is the Example sheet that I need to copy the data from and the Corrected Sheet that I need the data pasted into.

Should the data be copied one below the other or in certain columns? Do you have a sample file that you can upload?

Sure, I've uploaded a sample file of the old version with the incorrect comp day calculations in it.

What is the format for the new file? What will be the headers? Maybe you can attach a sample output file where the data should be copied.

What is the error in the original? It might be easier to update the formulas in the existing workbook. Another suggestion for you: it sounds like there could be quite a few of these workbooks and they all contain VBA code. That means if your code ever fails, you have to correct it in each workbook. If instead you create an add-in (just save a blank workbook as an add-in someplace where all users would have access) then add that add-in as a reference in your VBA project, you can do all your coding in one place...and just one place to do updates if needed later on.

This is how I would replace the formulas in the bad workbooks.

Please Login or Register  to view this content.

Sorry...as you posted some VBA code originally I took it for granted that you would know how to ammend my code. You'll need to click ALT +F11 to go into the VB Editor and the navigate to the module titled modCORRECTIONS. There, you'll find the code I posted in #10 post above. If the user's copy of the workbook is protected change the value "YOUR WORKBOOK PASSWORD HERE" to match your workbook password. Then remove the apostrophe on this line

Please Login or Register  to view this content.

and this line

Please Login or Register  to view this content.

If your individual worksheets are protected change the value "YOUR WORKSHEET PASSWORD HERE" to match your worksheet password. Then remove the apostrophe on these two lines of code

Please Login or Register  to view this content.

If you want to have the program save the users fixed workbook, then remove the apostrophe in front of "wbbad.save" as well. If you send this file out to your users they can then run the macro from the button and correct the formulas in their own workbooks. If not all worksheets are protected (ie. there are maybe hidden sheets you didn't protect) this code will still fail, so let me know if that's the case and I'll be happy to help you amend this code. But if the structure of your corrected workbook you posted matches the structure of the user's incorrect copies, this is, in my humble opinion, the better solution.

Here's the value update solution as well (just so you can see how it could be done...I still recommend using the CorrectFormulas method I posted previously). This has the same ammendment needs as the prior code

Please Login or Register  to view this content.

Hi chichapher. lCalc is a variable I use to store the setting of Application.Calculation and then restore the original setting of Application.Calculation back to what is was. Not everyone has autocalc turned on, so it's not a good idea to mess with their settings. Doing it this way allows you to see what they had and then put it back the way it was.

The code I posted doesn't use a copy/paste method. It sets values of one range = to values of another range. This is the line doing the work

Please Login or Register  to view this content.

If you want range B1:B3 copied from the master tab to each of the other tabs alter this section

Please Login or Register  to view this content.

I really enjoyed John Walkenbach's Power Programming With VBA (I read the Excel 2003 edition, but you can get 2007 and 2010 as well). Once you start coding, this forum is one of the best resources you'll find.

PS. I'm a 1989 Hobbs High grad Always happy to help a fellow New Mexican!