| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA problem
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA problem
|
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA problem
|
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA problem
Don, I'm using Excel 2007 and I find nothing in the "Prepare" area that refers to links. Possibly a little more specifics might help. Thanks, Bob "Don Guillett" wrote: your toolbar has an item called EDIT. A sub menu of that is LINKS. See if there are links posted to break. -- Don Guillett Microsoft MVP Excel SalesAid Software "robert morris" wrote in message ... Don, Thanks for answering. Edit Links where? I'm very new at this. Bob "Don Guillett" wrote: editlinks -- Don Guillett Microsoft MVP Excel SalesAid Software "robert morris" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA problem
Don, After several hours I still cannot find a reference to EDIT LINKS in Excel 2007. I am desperate for help. It's obvious the Link is lost because the Tabs have reverted to Sheet 1 instead of Sheet 79. I need help! Bob "Don Guillett" wrote: your toolbar has an item called EDIT. A sub menu of that is LINKS. See if there are links posted to break. -- Don Guillett Microsoft MVP Excel SalesAid Software "robert morris" wrote in message ... Don, Thanks for answering. Edit Links where? I'm very new at this. Bob "Don Guillett" wrote: editlinks -- Don Guillett Microsoft MVP Excel SalesAid Software "robert morris" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA problem
robert morris -
In Excel 2007, choose Data | Connections | Edit Links. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "robert morris" wrote in message ... Don, After several hours I still cannot find a reference to EDIT LINKS in Excel 2007. I am desperate for help. It's obvious the Link is lost because the Tabs have reverted to Sheet 1 instead of Sheet 79. I need help! Bob "Don Guillett" wrote: your toolbar has an item called EDIT. A sub menu of that is LINKS. See if there are links posted to break. -- Don Guillett Microsoft MVP Excel SalesAid Software "robert morris" wrote in message ... Don, Thanks for answering. Edit Links where? I'm very new at this. Bob "Don Guillett" wrote: editlinks -- Don Guillett Microsoft MVP Excel SalesAid Software "robert morris" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA problem
Mike,
Thanks, Edit Links under Connections is "greyed out" I keep getting an error #1004 or the Red button with the X that says 400 I'm in desperate need of help. My project has to be finished by Monday and with a working VBA it still will take me 12 - 15 hours Can you help? Bob "Mike Middleton" wrote: robert morris - In Excel 2007, choose Data | Connections | Edit Links. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "robert morris" wrote in message ... Don, After several hours I still cannot find a reference to EDIT LINKS in Excel 2007. I am desperate for help. It's obvious the Link is lost because the Tabs have reverted to Sheet 1 instead of Sheet 79. I need help! Bob "Don Guillett" wrote: your toolbar has an item called EDIT. A sub menu of that is LINKS. See if there are links posted to break. -- Don Guillett Microsoft MVP Excel SalesAid Software "robert morris" wrote in message ... Don, Thanks for answering. Edit Links where? I'm very new at this. Bob "Don Guillett" wrote: editlinks -- Don Guillett Microsoft MVP Excel SalesAid Software "robert morris" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
| problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Linear Mode
