Macro to open, copy data and rename from 1 workbook to another

[granita1]

Hi, I’m new to macros and was wondering if anyone could help me with the following, I have tried to record a macro but it takes way too long and I can’t get it to open a workbook. I have 2 workbooks, the first (contains data entered day by day) called “SolarTracker”, the second is almost the same as the first apart from the way in which calculations are made called “SolarTrackerRev”, both are located in My Documents. Each workbook has the same 4 worksheets, “Setup Sheet”, “Data Sheet”, Summary Sheet” and “Graphs”. Each time I make changes to the way things are calculated I would like to use a macro to copy data from the “SolarTracker” workbook across to the “SolarTrackerRev” workbook. There are specific ranges that I would like transferred as listed below and these are transferred to the exact same cells and same worksheet in the second workbook. I would like the macro to operate from the “SolarTrackerRev” workbook, and would like it to automatically open the “SolarTracker” workbook, transfer the data to the “SolarTrackerRev” workbook, close the “SolarTracker” workbook then rename the “SolarTracker Rev” workbook as “SolarTracker” effectively overwriting the original “SolarTracker” workbook.
Ranges to be copied across are:
“Setup Sheet”:
G9
G12:G14
I12:I14
G16:G18
I16:I18
G24:G26
I24:I26
E28
I28:I29

“Data Sheet”
B11
C12:E1201
L11:L1201
N11:N1201
P11:P1201

[JBeaucaire]

Give this a try...edit the wbStFullName string at the top to include the full pathname and filename to where your SolarTracker.xls file resides on your computer.

Please Login or Register  to view this content.

[granita1]

Thank Jerry, will give that a try.

[granita1]

Jerry,
Have tried the macro but it comes up with Runtime Error '1004' and to check the name of the file.
When debugging it highlights the following out of the macro,

Set wbST = Workbooks.Open(wbSTFullName)

The file is located in my documents, but i would also like to be able to do the same macro under other users. Any suggestions? One more thing is that the workbooks are macro enabled (xlsm file).

Regards
Grant

[JBeaucaire]

What did you edit the wbStFullName variable at the top of the macro to be?

If you want the system to "find" My Documents on its own, change that line of code to this:

Please Login or Register  to view this content.

[granita1]

Thanks Jerry, worked a treat

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[granita1]

Thanks a million Jerry