Change links/reference a new file

[Guest]

Hi,

I have a Master workbook that comprises numerous cost centre sheets. Each
month, I run a macro that copies each cost centre sheet into a new workbook.

I now have to include a summary sheet in each new cost centre workbook. The
summary sheet is also contained in the master workbook, and the formulae look
at the 'Total' sheet of the Master workbook.

The problem I face is that when I copy the summary sheet and each cost
centre sheet into new workbooks, the summary sheets continue to look at the
Master workbook Total sheet. I have tried using edit=>links=>change source,
but I get an error message stating the new file is not a valid reference.

I need some code to include in the original macro that will change the
reference from the Master workbook to the new workbook, but it should be
generic as the workbook has been designed for use by any unit with different
cost centres - the names of which are always different.

Please can someone offer advice? I can include some code to make things
clearer if needed.

Thanks in advance,

Ewan.

 

[Mike Fogleman]

Has the new workbook been saved yet before you tried to change the source to
itself?

Mike F

 

[Guest]

Mike,

Yes the file has been saved before changing the source. This is the code I
have currently:

Dim CELL As Range, RNG As Range ' --this is a lookup for each cost centre
sheet
With Worksheets("SETUP SHEET")
Set RNG = .Range(.Range("C3:C52"), .Range("C3:C52").End(xlDown))
End With

Application.ScreenUpdating = False
ActiveWorkbook.SaveAs Filename:=Sheets("INSTRUCTIONS").Range("A11").Value &
".xls"
For Each CELL In RNG
If CELL <> "BLANKS" Then
If CELL <> "" Then
Sheets(Array("SUMMARY", CELL.Value)).Copy '--this copies the
cost centre and summary
Sheets(CELL.Value).Select
ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".xls"
'--this saves the new workbook as the name of the cost centre
Sheets("SUMMARY").Select
Range("C9:R47").Select
Selection.Replace What:="'[MASTER WORKBOOK]TOTAL'", _
Replacement:=Sheets(CELL.Value), LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveWorkbook.Close
End If
End If
Next
Beep
MsgBox "Your sheets have been copied."

End Sub


Please let me know if there's anything else I can explain,

Ewan

 

[Guest]

Mike,

I have worked it out. I created variables for the master workbook and the
target worksheet in the new workbook, then replaced one with the other using
selection.replace. The complete code is:

Sub COPY_SHEETS_N_SAVE()

Dim WBOOK As String, SHT As String
Dim CELL As Range, RNG As Range

With Worksheets("SETUP SHEET")
Set RNG = .Range(.Range("C3:C52"), .Range("C3:C52").End(xlDown))
End With

Application.StatusBar = "Please wait while your sheets are copied to your
HOME folder..."
Application.ScreenUpdating = False
ActiveWorkbook.SaveAs Filename:=Sheets("ACTUALS
INSTRUCTIONS").Range("A11").Value & Sheets("ACTUALS
INSTRUCTIONS").Range("B3").Value ".xls"
WBOOK = ThisWorkbook.Name
For Each CELL In RNG
If CELL <> "BLANKS" Then
If CELL <> "" Then
Sheets(Array("SUMMARY", CELL.Value)).Copy
Sheets(CELL.Value).Select
SHT = ActiveSheet.Name
ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name &
Range("C2").Value ".xls"
Sheets("SUMMARY").Select
Range("C9:R47").Select
Selection.Replace What:="'[" & WBOOK & "]TOTAL'", _
Replacement:="'" & SHT & "'", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End If
Next
Beep
MsgBox "Your sheets have been copied."
Application.StatusBar = False


End Sub

Mike,

Yes the file has been saved before changing the source. This is the code I
have currently:

Dim CELL As Range, RNG As Range ' --this is a lookup for each cost centre
sheet
With Worksheets("SETUP SHEET")
Set RNG = .Range(.Range("C3:C52"), .Range("C3:C52").End(xlDown))
End With

Application.ScreenUpdating = False
ActiveWorkbook.SaveAs Filename:=Sheets("INSTRUCTIONS").Range("A11").Value &
".xls"
For Each CELL In RNG
If CELL <> "BLANKS" Then
If CELL <> "" Then
Sheets(Array("SUMMARY", CELL.Value)).Copy '--this copies the
cost centre and summary
Sheets(CELL.Value).Select
ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".xls"
'--this saves the new workbook as the name of the cost centre
Sheets("SUMMARY").Select
Range("C9:R47").Select
Selection.Replace What:="'[MASTER WORKBOOK]TOTAL'", _
Replacement:=Sheets(CELL.Value), LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveWorkbook.Close
End If
End If
Next
Beep
MsgBox "Your sheets have been copied."

End Sub


Please let me know if there's anything else I can explain,

Ewan

Has the new workbook been saved yet before you tried to change the source to
itself?

Mike F