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

user_1048539 0
Viewed 191548

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!"
vba excel
8 Answers

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_115400450

TRY This if your first sheet is not visible

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

user_198935610
Related