Creating multiple copies of a worksheet

Topic

New Reply

I have a module where I have to create multiple copies of a worksheet and put them in order, and I am using code like this:

Code:

    Sheets("x").Copy After:=Sheets("x")
    Sheets("x (2)").Name = "y"
Set w3 = Sheets("y")

    Sheets("y").Copy After:=Sheets("y")
    Sheets("y (2)").Name = "z"
Set w4 = Sheets("z")

As this occurs numerous times, is there a more compact approach?

Reply | Quote

Replies

Does something like this work?

Code:

Option Explicit
Sub CopySheets()
  Dim wks(0 To 4) As Worksheet
  Dim vNames
  Dim i As Integer
  
'Change and add as desired
  vNames = Array("x", "a", "b", "y", "z")

  Set wks(0) = Worksheets(vNames(0))
  For i = 1 To UBound(vNames)
	wks(0).Copy After:=Worksheets(vNames(i - 1))
	Set wks(i) = ActiveSheet
	wks(i).Name = vNames(i)
  Next
End Sub

Change/add to names of the sheets to change how many. The first sheet (item 0 = “x”) is the existing original sheet all the others are the copies

Steve

Reply | Quote

NB: Copying the copy of the copy of the copy of a sheet has led to problems in previousl Excel versions (I think it was fixed in Excel 2000 SP3). It might be better to keep using the same original and copy that sheet repeatedly.

Reply | Quote

Thanks both. Steve’s solution worked a treat.

Reply | Quote

Note that my code does not copy the copied worksheets as Jan warned against, it continually copies the same original…

Steve

Reply | Quote