Hi There, I have been searching through solutions for copying and moving a specific worksheet in my workbook (“Template”) to the very last sheet or behind the last sheet. Because I will eventually have many sheets (more than 30) I have opted to hide them (very hidden) and the users selects them from hyperlinks on a main page, after use and selecting a different sheet the active sheet that the user worked on goes back to very hidden.
Anyway, the problem I have is that when sheets are very hidden the code below cannot pick that up and just copies the “Template” sheet to the next visible sheet and not at the end of the last string of hidden sheets – hope that makes sense.
I came accords some Functional ideas but I would like to generate this through a command button when needed rather than a function. I would also like to keep the option where a message box pops up and the user can type the name of the new sheet
Sub Copy_Workbook_VeryLast()
Dim Answer As Variant
lastSheet = Sheets.Count
Sheets("Template").Copy after:=Sheets(lastSheet)
Answer = InputBox(prompt:="What do want to name this sheet. The name must contain no spaces. If space is required use underscore ( _ e.g New_Name)", _
Title:=" We need a Name for this Sheet")
If Answer = "" Then Exit Sub
ActiveSheet.Name = Answer
End Sub
Bookmarks