Not following the question entirely, but I see you have defined a "sub" and a
"private sub". Do you have issues with the new file not being able to
execute the code in the private sub?

What do you mean by "changing the macro at the end of VBA"?

What exactly are you trying to accomplish?

Let me know and I might be able to help...just not sure I understand.



"robert morris" wrote:


This is a re-post from earlier. My LINKS have broken and now reverted back
to Sheet #1 instead of Sheet #79

Here is the original post;

The code below works for one Workbook. Problem is, I copied the "Standings"
and a "Master Sheet" to a new Workbook with a different name "Region 2".

The NEW workbook keeps changing the Macro posted at the end of this VBA and
nothing works. I can delete both VBA and Macro from the NEW worksheet
"Master" and the first Workbook works. When I want to work on the NEW w/b, I
copy the VBA and Macro into the NEW w/b and delete same from the original.
This allows the NEW book to function but obviously the original w/b no longer
functions.

I'm guessing the problem has something to do with the copying. Also I'm
seeing a Red button with an "x" and error 400. Using Office 2007

Sub MakeLinks()
Dim myR As Long
With Worksheets("Standings")
myR = .Range("F" & Rows.Count).End(xlUp)(2).Row
.Range("F" & myR).Formula = "='" & ActiveSheet.Name & "'!M75"
.Range("H" & myR).Formula = "='" & ActiveSheet.Name & "'!D75"
.Range("I" & myR).Formula = "='" & ActiveSheet.Name & "'!F73"
.Range("J" & myR).Formula = "='" & ActiveSheet.Name & "'!H73"
.Range("K" & myR).Formula = "='" & ActiveSheet.Name & "'!J73"
.Range("L" & myR).Formula = "='" & ActiveSheet.Name & "'!L73"
.Range("M" & myR).Formula = "='" & ActiveSheet.Name & "'!M71"
.Range("C" & myR).Formula = "='" & ActiveSheet.Name & "'!B2"
End With
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$B$2" Then Sh.Name = Target.Value
End Sub

Macro is - Sheet2.MakeLinks - Macros in: This Workbook


Bob M.