2007 vba - Duplicate worksheet

[VegasNath]

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?

[HansV]

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

[VegasNath]

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?

[HansV]

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:

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

[VegasNath]

Brill, Thanks