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.

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.

GuruGirl

Thanks, I'm the only user of this workbook so it does not make a difference
to me as to "sub" and/or 'private sub"

What I have is a Workbook with a worksheet titled "Summary" which is the
destination and a worksheet titled "Blank Master" which is the source. I
copy the "Blank Master" to a new Tab and rename the new Tab sheet in B2 which
also renames the Tab to the new name. I have about 100 worksheets which have
all been renamed. These w/s's are updated to the "Summary" w/s.

I have also copied the Original "Summary" w/s and the "Blank Master" to a
new Workbook which I named Region 2. I do not know if this could be part of
the problem or not.

It all worked fine until something went wrong and I can no longer post to
either Workbook.

I'm just very confused about VBA at this time.

I hope this helps a little.

Bob


"GuruGirl" wrote:

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.

Did you receive my reply? The web page failed. i am not sure.

"robert morris" wrote:

GuruGirl

Thanks, I'm the only user of this workbook so it does not make a difference
to me as to "sub" and/or 'private sub"

What I have is a Workbook with a worksheet titled "Summary" which is the
destination and a worksheet titled "Blank Master" which is the source. I
copy the "Blank Master" to a new Tab and rename the new Tab sheet in B2 which
also renames the Tab to the new name. I have about 100 worksheets which have
all been renamed. These w/s's are updated to the "Summary" w/s.

I have also copied the Original "Summary" w/s and the "Blank Master" to a
new Workbook which I named Region 2. I do not know if this could be part of
the problem or not.

It all worked fine until something went wrong and I can no longer post to
either Workbook.

I'm just very confused about VBA at this time.

I hope this helps a little.

Bob


"GuruGirl" wrote:

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.

Robert,

I guess my post failed. Ratz. Anyway....

1. You need to first obtain the name of the file you want to transfer the
sheet to. Hold it in a variable.

2. You will need to copy the sheet in your existing file, then MOVE it from
your existing file to the new file. In the move command you will pass the
open file name of the new file to the code and it will know where to move the
sheet to.

3. if your code is failing at the point of copy, it is probably because you
are not giving it the open file name in a way it understands.

4. Additionally Range Names copy from sheet to sheet from file to file,
same same, so if you have code executing in reference to a range name you
need to specify a different filename.

I have some code example, but lost it when my response did not post. Let me
know if you want the example or if you are fine from here.







"robert morris" wrote:

GuruGirl

Thanks, I'm the only user of this workbook so it does not make a difference
to me as to "sub" and/or 'private sub"

What I have is a Workbook with a worksheet titled "Summary" which is the
destination and a worksheet titled "Blank Master" which is the source. I
copy the "Blank Master" to a new Tab and rename the new Tab sheet in B2 which
also renames the Tab to the new name. I have about 100 worksheets which have
all been renamed. These w/s's are updated to the "Summary" w/s.

I have also copied the Original "Summary" w/s and the "Blank Master" to a
new Workbook which I named Region 2. I do not know if this could be part of
the problem or not.

It all worked fine until something went wrong and I can no longer post to
either Workbook.

I'm just very confused about VBA at this time.

I hope this helps a little.

Bob


"GuruGirl" wrote:

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.

Did not receive your reply except for this one.

Bob

"GuruGirl" wrote:

Did you receive my reply? The web page failed. i am not sure.

"robert morris" wrote:

GuruGirl

Thanks, I'm the only user of this workbook so it does not make a difference
to me as to "sub" and/or 'private sub"

What I have is a Workbook with a worksheet titled "Summary" which is the
destination and a worksheet titled "Blank Master" which is the source. I
copy the "Blank Master" to a new Tab and rename the new Tab sheet in B2 which
also renames the Tab to the new name. I have about 100 worksheets which have
all been renamed. These w/s's are updated to the "Summary" w/s.

I have also copied the Original "Summary" w/s and the "Blank Master" to a
new Workbook which I named Region 2. I do not know if this could be part of
the problem or not.

It all worked fine until something went wrong and I can no longer post to
either Workbook.

I'm just very confused about VBA at this time.

I hope this helps a little.

Bob


"GuruGirl" wrote:

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.

GuruGirl,

Again, many thanks, you better try sending that code again. I have put a
band aid on the problem by deleting the codes from (let's say) Workbook A,
posting to workbook B, deleting the codes from w/b B, re-enter the codes to
w/b A, .........

"GuruGirl" wrote:

Robert,

I guess my post failed. Ratz. Anyway....

1. You need to first obtain the name of the file you want to transfer the
sheet to. Hold it in a variable.

2. You will need to copy the sheet in your existing file, then MOVE it from
your existing file to the new file. In the move command you will pass the
open file name of the new file to the code and it will know where to move the
sheet to.

3. if your code is failing at the point of copy, it is probably because you
are not giving it the open file name in a way it understands.

4. Additionally Range Names copy from sheet to sheet from file to file,
same same, so if you have code executing in reference to a range name you
need to specify a different filename.

I have some code example, but lost it when my response did not post. Let me
know if you want the example or if you are fine from here.







"robert morris" wrote:

GuruGirl

Thanks, I'm the only user of this workbook so it does not make a difference
to me as to "sub" and/or 'private sub"

What I have is a Workbook with a worksheet titled "Summary" which is the
destination and a worksheet titled "Blank Master" which is the source. I
copy the "Blank Master" to a new Tab and rename the new Tab sheet in B2 which
also renames the Tab to the new name. I have about 100 worksheets which have
all been renamed. These w/s's are updated to the "Summary" w/s.

I have also copied the Original "Summary" w/s and the "Blank Master" to a
new Workbook which I named Region 2. I do not know if this could be part of
the problem or not.

It all worked fine until something went wrong and I can no longer post to
either Workbook.

I'm just very confused about VBA at this time.

I hope this helps a little.

Bob


"GuruGirl" wrote:

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.