[vba] VBA Copy Sheet to End of Workbook (with Hidden Worksheets)

I want to copy a sheet and add it to the end of all current sheets (regardless of whether the sheets are hidden).

Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).name = "copied sheet!"

This works fine, except, when there are hidden sheets, the new sheet is only inserted after the last visible worksheet, so the name command renames the wrong sheet.

I have tried variations of the following to get a reference to the newly copied WorkSheet but none were successful and/or valid code.

Dim test As Worksheet
Set test = Sheets(1).Copy(After:=Sheets(Sheets.Count))
test.Name = "copied sheet!"

Answer : I found this and wants to share it with you.

Sub Copier4()
   Dim x As Integer

   For x = 1 To ActiveWorkbook.Sheets.Count
      'Loop through each of the sheets in the workbook
      'by using x as the sheet index number.
      ActiveWorkbook.Sheets(x).Copy _
         After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
         'Puts all copies after the last existing sheet.
   Next
End Sub

But the question, can we use it with following code to rename the sheets, if yes, how can we do so?

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Summary").Range("A10")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

I faced a similar issue while copying a sheet to another workbook. I prefer to avoid using 'activesheet' though as it has caused me issues in the past. Hence I wrote a function to perform this inline with my needs. I add it here for those who arrive via google as I did:

The main issue here is that copying a visible sheet to the last index position results in Excel repositioning the sheet to the end of the visible sheets. Hence copying the sheet to the position after the last visible sheet sorts this issue. Even if you are copying hidden sheets.

Function Copy_WS_to_NewWB(WB As Workbook, WS As Worksheet) As Worksheet
    'Creates a copy of the specified worksheet in the specified workbook
    '   Accomodates the fact that there may be hidden sheets in the workbook
    
    Dim WSInd As Integer: WSInd = 1
    Dim CWS As Worksheet
    
    'Determine the index of the last visible worksheet
    For Each CWS In WB.Worksheets
        If CWS.Visible Then If CWS.Index > WSInd Then WSInd = CWS.Index
    Next CWS
    
    WS.Copy after:=WB.Worksheets(WSInd)
    Set Copy_WS_to_NewWB = WB.Worksheets(WSInd + 1)

End Function

To use this function for the original question (ie in the same workbook) could be done with something like...

Set test = Copy_WS_to_NewWB(Workbooks(1), Workbooks(1).Worksheets(1))
test.name = "test sheet name"

EDIT 04/11/2020 from –user3598756 Adding a slight refactoring of the above code

Function CopySheetToWorkBook(targetWb As Workbook, shToBeCopied As Worksheet, copiedSh As Worksheet) As Boolean
    'Creates a copy of the specified worksheet in the specified workbook
    '   Accomodates the fact that there may be hidden sheets in the workbook

    Dim lastVisibleShIndex As Long
    Dim iSh As Long

    On Error GoTo SafeExit
    
    With targetWb
        'Determine the index of the last visible worksheet
        For iSh = .Sheets.Count To 1 Step -1
            If .Sheets(iSh).Visible Then
                lastVisibleShIndex = iSh
                Exit For
            End If
        Next
    
        shToBeCopied.Copy after:=.Sheets(lastVisibleShIndex)
        Set copiedSh = .Sheets(lastVisibleShIndex + 1)
    End With
    
    CopySheetToWorkBook = True
    Exit Function
    
SafeExit:
    
End Function

other than using different (more descriptive?) variable names, the refactoring manily deals with:

1. turning the Function type into a `Boolean while including returned (copied) worksheet within function parameters list this, to let the calling Sub hande possible errors, like

    Dim WB as Workbook: Set WB = ThisWorkbook ' as an example
    Dim sh as Worksheet: Set sh = ActiveSheet ' as an example
    Dim copiedSh as Worksheet
    If CopySheetToWorkBook(WB, sh, copiedSh) Then
        ' go on with your copiedSh sheet
    Else
        Msgbox "Error while trying to copy '" & sh.Name & "'" & vbcrlf & err.Description
    End If
   

2. having the For - Next loop stepping from last sheet index backwards and exiting at first visible sheet occurence, since we're after the "last" visible one

Make the source sheet visible before copying. Then copy the sheet so that the copy also stays visible. The copy will then be the active sheet. If you want, hide the source sheet again.

When you want to copy a sheet named "mySheet" and use .Copy After:=, Excel first names the copied sheet exactly the same and simply adds ' (2)' so that its final name is "mySheet (2)".

Hidden or Not, doesn't matter. It rocks with 2 lines of code, adding the copied sheet at the end of the Workbook!!!

Example:

Sheets("mySheet").Copy After:=Sheets(ThisWorkbook.Sheets.count)
Sheets("mySheet (2)").name = "TheNameYouWant"

Simple no!

Add this code to the beginning:

    Application.ScreenUpdating = False
     With ThisWorkbook
      Dim ws As Worksheet
       For Each ws In Worksheets: ws.Visible = True: Next ws
     End With

Add this code to the end:

    With ThisWorkbook
     Dim ws As Worksheet
      For Each ws In Worksheets: ws.Visible = False: Next ws
    End With
     Application.ScreenUpdating = True

Adjust Code at the end if you want more than the first sheet to be active and visible. Such as the following:

     Dim ws As Worksheet
      For Each ws In Worksheets
       If ws.Name = "_DataRecords" Then

         Else: ws.Visible = False
       End If
      Next ws

To ensure the new sheet is the one renamed, adjust your code similar to the following:

     Sheets(Me.cmbxSheetCopy.value).Copy After:=Sheets(Sheets.Count)
     Sheets(Me.cmbxSheetCopy.value & " (2)").Select
     Sheets(Me.cmbxSheetCopy.value & " (2)").Name = txtbxNewSheetName.value

This code is from my user form that allows me to copy a particular sheet (chosen from a dropdown box) with the formatting and formula's that I want to a new sheet and then rename new sheet with the user Input. Note that every time a sheet is copied it is automatically given the old sheet name with the designation of " (2)". Example "OldSheet" becomes "OldSheet (2)" after the copy and before the renaming. So you must select the Copied sheet with the programs naming before renaming.

If you use the following code based on @Siddharth Rout's code, you rename the just copied sheet, no matter, if it is activated or not.

Sub Sample()

    ThisWorkbook.Sheets(1).Copy After:=Sheets(Sheets.Count)
    ThisWorkbook.Sheets(Sheets.Count).Name = "copied sheet!"

End Sub