Update macro path after workbooks moved, renamed

**jfreeman5** · 02-03-2011, 01:56 PM

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

**royUK** · 02-03-2011, 02:06 PM

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