Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Note that my code does not copy the copied worksheets as Jan warned against, it continually copies the same original...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •