xl-vba: Copy hidden sheet and manipulate it

[lalbatros]

Hello,

I want to copy a hidden template sheet, using VBA.
I do not want to make this template visible before I finish making some modifications to it like: naming it and writing a few things onto it.

I was unable to do that.
This is simply because I cannot Get the sheet to make these modifications.

The copied sheet doesn't become active since it is hidden.
The name of the hidden sheet is not easily predictable.
The Copy method does not return a sheet object, unfortunately.

How would you proceed to get the copy?

Thanks,

Michel

 

[Richard Schollar]

Hi Michel

If you copy the sheet and use the Before argument then you can be sure the copied sheet will be returned to index 1 in the workbook, then you know where the sheet is:

Sheets(Sheets.Count).Copy Before:=Sheets(1)   'copying the last sheet in the workbook - amend as appropriate

Msgbox "Copied sheet's name is " & Sheets(1).Name