VBA Copy Sheet to End of Workbook with Hidden Worksheets

Question

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

User · Answer

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

User · Answer

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  amp     2    Select      Sheets Me cmbxSheetCopy value  amp     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

User · Answer

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

User · Answer

Try this  Sub Sample       Dim test As Worksheet     Sheets 1  Copy After  Sheets Sheets Count      Set test   ActiveSheet     test Name    copied sheet   End Sub

User · Answer

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  gt  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    quot test sheet name quot    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   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  quot Error while trying to copy   quot   amp  sh Name  amp   quot   quot   amp  vbcrlf  amp  err Description  End If   having the For - Next loop stepping from last sheet index backwards and exiting at first visible sheet occurence  since we re after the  quot last quot  visible one

User · Answer

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

User · Answer

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

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

Examples related to vba

Examples related to excel