Please help. I have developed a macro to export data from each monthly workbook to a yearly workbook. It works good in the master template folder however i need to develop a procedure to copy the master documents to a new folder for every year. so i would take the folder called 'master' with my genericlly named jan 20xx.xlsm, feb 20xx.xlsm etc, and lab yearly master.xlsm and rename the folder 2011 and rename the monthlys jan 2011.xlsm, feb 2011xlsm, etc, and yearly report 2011.xlsm. The problem is how do i update the links so that the macros still work when i export and don't try to export into the old master yearly report. There must be an easier way than to go into the macro in every renamed monthly workbook and change the paths. I am using ThisWorkbook for the monthlys so i dont think changing their names are an issue.
The only issue is getting
Set bk = Workbooks.Open("S:\Plant 3\P3-Monthly Reports\Oper-State\Master\lab yearly master.xlsm")
changed to
S:\Plant 3\P3-Monthly Reports\Oper-State\2011\yearly report 2011
Is there a macro i can use to update all the macros in the monthly workbooks with the correct path? or is there a way to better write this to update on a saveas for example?
below is the entire macro
Thankyou
John
Sub January()
If MsgBox("EXPORT DATA TO YEARLY REPORT BEFORE CLOSING?", vbYesNo, _
"EXPORT") = vbNo Then Exit Sub
Dim bk As Workbook
Set bk = Workbooks.Open("S:\Plant 3\P3-Monthly Reports\Oper-State\Master\lab yearly master.xlsm")
bk.Activate
ThisWorkbook.Activate
Sheets("P1").Select
Range("C9:C39").Select
Selection.Copy
bk.Activate
Sheets("INFLUENT").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("D9:D39").Select
Selection.Copy
bk.Activate
Sheets("INFLUENT").Select
Range("M5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False '''
ThisWorkbook.Activate
Sheets("P1").Select
Range("E9:E39").Select
Selection.Copy
bk.Activate
Sheets("PRIMARY").Select
Range("J6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False '''
ThisWorkbook.Activate
Sheets("P1").Select
Range("F9:F39").Select
Selection.Copy
bk.Activate
Sheets("SECONDARY").Select
Range("J6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False '''''
ThisWorkbook.Activate
Sheets("P1").Select
Range("G9:G39").Select
Selection.Copy
bk.Activate
Sheets("TERTIARY EFFLUENT").Select
Range("D5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ''''''''
ThisWorkbook.Activate
Sheets("P1").Select
Range("H9:H39").Select
Selection.Copy
bk.Activate
Sheets("INFLUENT").Select
Range("P5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ''
ThisWorkbook.Activate
Sheets("P1").Select
Range("I9:I39").Select
Selection.Copy
bk.Activate
Sheets("PRIMARY").Select
Range("K6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False '''''''
ThisWorkbook.Activate
Sheets("P1").Select
Range("J9:I39").Select
Selection.Copy
bk.Activate
Sheets("SECONDARY").Select
Range("AE6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("K9:K39").Select
Selection.Copy
bk.Activate
Sheets("TERTIARY EFFLUENT").Select
Range("I5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("l9:l39").Select
Selection.Copy
bk.Activate
Sheets("INFLUENT").Select
Range("H5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("M9:M39").Select
Selection.Copy
bk.Activate
Sheets("PRIMARY").Select
Range("I6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("N9:N39").Select
Selection.Copy
bk.Activate
Sheets("SECONDARY").Select
Range("N6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("O9:O39").Select
Selection.Copy
bk.Activate
Sheets("TERTIARY EFFLUENT").Select
Range("H5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("P9:P39").Select
Selection.Copy
bk.Activate
Sheets("INFLUENT").Select
Range("I5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("Q9:Q39").Select
Selection.Copy
bk.Activate
Sheets("PRIMARY").Select
Range("O6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("R9:R39").Select
Selection.Copy
bk.Activate
Sheets("SECONDARY").Select
Range("G6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("S9:S39").Select
Selection.Copy
bk.Activate
Sheets("TERTIARY EFFLUENT").Select
Range("J5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("T9:T39").Select
Selection.Copy
bk.Activate
Sheets("INFLUENT").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("U9:U39").Select
Selection.Copy
bk.Activate
Sheets("PRIMARY").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("V9:V39").Select
Selection.Copy
bk.Activate
Sheets("SECONDARY").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("W9:W39").Select
Selection.Copy
bk.Activate
Sheets("TERTIARY EFFLUENT").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("X9:X39").Select
Selection.Copy
bk.Activate
Sheets("INFLUENT").Select
Range("F5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("Y9:Y39").Select
Selection.Copy
bk.Activate
Sheets("PRIMARY").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("Z9:Z39").Select
Selection.Copy
bk.Activate
Sheets("TERTIARY EFFLUENT").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("AE9:AE39").Select
Selection.Copy
bk.Activate
Sheets("INFLUENT").Select
Range("Q5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Sheets("P1").Select
Range("AF9:AF39").Select
Selection.Copy
bk.Activate
Sheets("SECONDARY").Select
Range("O6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True
End Sub
Last edited by jfreeman5; 02-03-2011 at 04:05 PM. Reason: comply with standard
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
fixed see above. would appreciate everyones help thanks
Last edited by jfreeman5; 02-03-2011 at 04:06 PM. Reason: double entered
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks