VB Script for Copy Sheet

  • Thread starter Thread starter magmike
  • Start date Start date
M

magmike

I am looking to create a button on a "summary" sheet that will, on the first click, unhide the quote page, and then on successive clicks, create a copy of the quote page and place it after the previous one. When I do that manually, I get a message about a named range and asks if I want to use the same name for the range or change it. So, I would need the code to address this issue, and keeping the same name for range on the new sheet is just fine..

I am open to suggestions on a better way to do this as well.

Thanks in advance for your help,

magmike
 
I am looking to create a button on a "summary" sheet that will, on
the first click, unhide the quote page, and then on successive
clicks, create a copy of the quote page and place it after the
previous one.
Click to expand...

Button clicks run procedures in entirety, as a rule, and so where the
copy goes depends on your logic. Normally, new sheets are inserted at
front or rear of existing sheet tabs. Optionally, you can pre-select
the sheet tab of where to insert (before/after) the copy.
When I do that manually, I get a message about a named
range and asks if I want to use the same name for the range or change
it. So, I would need the code to address this issue, and keeping the
same name for range on the new sheet is just fine.

I am open to suggestions on a better way to do this as well.
Click to expand...


First recommendation is to give your named range local scope (sheet
level) so the name conflict becomes a non-issue. Note that ALL defined
names should have local scope (unless absolutely unavoidable) as a
'best practice'!

Also, the copy will likely require some naming convention be
implemented so the sheetname is not just "Quote (n)" in the tab strip.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
First recommendation is to give your named range local scope (sheet
level) so the name conflict becomes a non-issue. Note that ALL
defined names should have local scope (unless absolutely unavoidable)
as a 'best practice'!
Click to expand...

Forgot to mention that depending on how you answer the name conflict
notification, all other sheets with the same 'global' name ref will now
auto ref the name as used on the copied sheet! Thus, the reason why
using local scope unless absolutely necessary is considered 'best
practice'!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Button clicks run procedures in entirety, as a rule, and so where the
copy goes depends on your logic. Normally, new sheets are inserted at
front or rear of existing sheet tabs. Optionally, you can pre-select
the sheet tab of where to insert (before/after) the copy.



First recommendation is to give your named range local scope (sheet
level) so the name conflict becomes a non-issue. Note that ALL defined
names should have local scope (unless absolutely unavoidable) as a
'best practice'!

Also, the copy will likely require some naming convention be
implemented so the sheetname is not just "Quote (n)" in the tab strip.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Click to expand...

How do rename a sheet via VB?
 
How do rename a sheet via VB?

Assign the name to its 'Name' property...

ActiveSheet.Name = "NewName"

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Mike,

Am Mon, 22 Dec 2014 08:46:20 -0800 (PST) schrieb magmike:
And then how could I loop the code to add 1 to name the next new sheet "Quote 2" if Quote 1 exists?
Click to expand...

try:

Sub test()
Dim wsh As Worksheet
Dim varSh() As Variant
Dim ShName As String
Dim n As Long

For Each wsh In Worksheets
If Left(wsh.Name, 5) = "Quote" And Len(wsh.Name) > 5 Then
ReDim Preserve varSh(n)
varSh(n) = CInt(Mid(wsh.Name, InStr(wsh.Name, " ") + 1))
n = n + 1
ElseIf wsh.Name = "Quote" Then
ShName = wsh.Name
End If
Next

If n = 0 And ShName = "" Then
MsgBox "Sheet Quote does not exist"
ElseIf Len(ShName) > 0 And n = 0 Then
Sheets.Add after:=Sheets("Quote")
ActiveSheet.Name = "Quote 1"
ElseIf n >= 1 Then
Sheets.Add after:=Sheets("Quote " & WorksheetFunction.Max(varSh))
ActiveSheet.Name = "Quote " & WorksheetFunction.Max(varSh) + 1
End If

End Sub


Regards
Claus B.
 
Hi Mike,

Am Mon, 22 Dec 2014 08:46:20 -0800 (PST) schrieb magmike:
And then how could I loop the code to add 1 to name the next new sheet "Quote 2" if Quote 1 exists?
Click to expand...

if you want to copy sheet "Quote" then try:

Sub test()
Dim wsh As Worksheet
Dim varSh() As Variant
Dim ShName As String
Dim n As Long

For Each wsh In Worksheets
If Left(wsh.Name, 5) = "Quote" And Len(wsh.Name) > 5 Then
ReDim Preserve varSh(n)
varSh(n) = CInt(Mid(wsh.Name, InStr(wsh.Name, " ") + 1))
n = n + 1
ElseIf wsh.Name = "Quote" Then
ShName = wsh.Name
End If
Next

If n = 0 And ShName = "" Then
MsgBox "Sheet Quote does not exist"
ElseIf Len(ShName) > 0 And n = 0 Then
Sheets("Quote").Copy After:=Sheets("Quote")
ActiveSheet.Name = "Quote 1"
ElseIf n >= 1 Then
Sheets("Quote").Copy After:=Sheets("Quote " &
WorksheetFunction.Max(varSh))
ActiveSheet.Name = "Quote " & WorksheetFunction.Max(varSh) + 1
End If

End Sub


Regards
Claus B.
 
I am looking to create a button on a "summary" sheet that will, on the first click, unhide the quote page, and then on successive clicks, create a copy of the quote page and place it after the previous one. When I do that manually, I get a message about a named range and asks if I want to use the same name for the range or change it. So, I would need the code to address this issue, and keeping the same name for range on the new sheet is just fine.

I am open to suggestions on a better way to do this as well.

Thanks in advance for your help,

magmike
Click to expand...

The Quote sheet will already exist as a template, but will be hidden. So how would we copy the hidden sheet, naming the first copy "Quote 1" and maintaining focus on the ActiveSheet, which is called "Summary"?
 
The Quote sheet will already exist as a template, but will be hidden. So how would we copy the hidden sheet, naming the first copy "Quote 1" and maintaining focus on the ActiveSheet, which is called "Summary"?
Click to expand...

and unhiding the new (copied) sheet?
 
Hi Mike,

Am Mon, 22 Dec 2014 10:13:02 -0800 (PST) schrieb magmike:
and unhiding the new (copied) sheet?
Click to expand...

try:

Sub test()
Dim wsh As Worksheet
Dim varSh() As Variant
Dim n As Long

For Each wsh In Worksheets
If Left(wsh.Name, 5) = "Quote" And Len(wsh.Name) > 5 Then
ReDim Preserve varSh(n)
varSh(n) = CInt(Mid(wsh.Name, InStr(wsh.Name, " ") + 1))
n = n + 1
End If
Next
Application.ScreenUpdating = False
If n = 0 Then
With Sheets("Quote")
.Visible = True
.Copy After:=Sheets("Quote")
ActiveSheet.Name = "Quote 1"
.Visible = False
End With
ElseIf n >= 1 Then
With Sheets("Quote")
.Visible = True
.Copy After:=Sheets("Quote " & WorksheetFunction.Max(varSh))
ActiveSheet.Name = "Quote " & WorksheetFunction.Max(varSh) + 1
.Visible = False
End With
End If
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
Hi Mike,

Am Mon, 22 Dec 2014 10:13:02 -0800 (PST) schrieb magmike:


try:

Sub test()
Dim wsh As Worksheet
Dim varSh() As Variant
Dim n As Long

For Each wsh In Worksheets
If Left(wsh.Name, 5) = "Quote" And Len(wsh.Name) > 5 Then
ReDim Preserve varSh(n)
varSh(n) = CInt(Mid(wsh.Name, InStr(wsh.Name, " ") + 1))
n = n + 1
End If
Next
Application.ScreenUpdating = False
If n = 0 Then
With Sheets("Quote")
.Visible = True
.Copy After:=Sheets("Quote")
ActiveSheet.Name = "Quote 1"
.Visible = False
End With
ElseIf n >= 1 Then
With Sheets("Quote")
.Visible = True
.Copy After:=Sheets("Quote " & WorksheetFunction.Max(varSh))
ActiveSheet.Name = "Quote " & WorksheetFunction.Max(varSh) + 1
.Visible = False
End With
End If
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
Click to expand...

This works great for the first copy. However, the second go around creates "Quote (2)" and gives the error that it cannot rename a sheet with the same name as another sheet. A press of Debug button takes me to the line of code:

ActiveSheet.Name = "Quote 1"

Which means that n still = 0 somehow
 
Hi Mike,

Am Mon, 22 Dec 2014 11:01:25 -0800 (PST) schrieb magmike:
This works great for the first copy. However, the second go around creates "Quote (2)" and gives the error that it cannot rename a sheet with the same name as another sheet. A press of Debug button takes me to the line of code:

ActiveSheet.Name = "Quote 1"

Which means that n still = 0 somehow
Click to expand...

I tested the code and did it again after your post. But for me it works
fine.


Regards
Claus B.
 
Hi Mike,

Am Mon, 22 Dec 2014 11:01:25 -0800 (PST) schrieb magmike:


I tested the code and did it again after your post. But for me it works
fine.


Regards
Claus B.
Click to expand...

I found my mistake. That works beautifully! Thanks!
 
Back
Top