R
robert morris
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
Any help for this?
Bob
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
Any help for this?
Bob