Hello,
Using 2007 vba, what is the best approcach to duplicating a worksheet various times and renaming them. I need to duplicate a worksheet 11 times and name them individually. Could this be done as an array?
2007 vba - Duplicate worksheet
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
2007 vba - Duplicate worksheet
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78592
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: 2007 vba - Duplicate worksheet
You'll have to loop, but you could use code like this (it's an example that creates only three duplicates):
Code: Select all
Sub DuplicateSheet()
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
Dim arrNames
Dim varItm
arrNames = Array("One", "Two", "Three")
Set wshSource = ActiveSheet ' or Worksheets("MySheet")
For Each varItm In arrNames
Set wshTarget = Worksheets.Add(After:=Worksheets(Worksheets.Count))
wshTarget.Name = varItm
Next varItm
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: 2007 vba - Duplicate worksheet
Hans, Thankyou. This only seems to create a blank worksheet and rename it. It does not contain the data of the source sheet.
I tried changing .add to .copy but I get a compile error?
I tried changing .add to .copy but I get a compile error?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78592
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: 2007 vba - Duplicate worksheet
Ack, didn't complete the code. I tried out creating new sheets first, then got distracted (happens easily at my age...)
Sorry about that!
Here is a corrected version:
Sorry about that!
Here is a corrected version:
Code: Select all
Sub DuplicateSheet()
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
Dim arrNames
Dim varItm
arrNames = Array("One", "Two", "Three")
Set wshSource = ActiveSheet ' or Worksheets("MySheet")
For Each varItm In arrNames
wshSource.Copy After:=Worksheets(Worksheets.Count)
Set wshTarget = Worksheets(Worksheets.Count)
wshTarget.Name = varItm
Next varItm
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.