worksheet copy problem
 

I have a workbook with a number of hidden sheets

When I copy one of my hidden sheets I do not necessarily no the name of the new sheet because of duplicates etc

I do not no the index number of the sheet because when I put it after sheets.count it goes after the last visible sheet

I would love to simply know the codename of the new sheet but again there can be "holes" in the codename numbering because of deletions

How can I copy a sheet and know its name, codename and index afterward?

worksheet copy problem
 

You might not be able to get the codename after you copy the sheet, but you may
not actually need it either.

If the VBE was opened, the codename was returned. If the VBE was never opened,
then the codename was returned as "".

Option Explicit
Sub testme()

Dim wkbk1 As Workbook
Dim wkbk2 As Workbook
Dim Wks As Worksheet

Set wkbk1 = Workbooks("book1.xls")
Set wkbk2 = Workbooks("book2.xls")

wkbk1.Worksheets(3).Copy _
after:=wkbk2.Worksheets(wkbk2.Worksheets.Count)
Set Wks = ActiveSheet

MsgBox Wks.Name & vbLf & Wks.CodeName & vbLf & Wks.Index

End Sub


But after you copy a worksheet, it's the activesheet. You can set an object
variable (wks in my example) to that new activesheet and do lots of stuff to it.


dan wrote:

I have a workbook with a number of hidden sheets.

When I copy one of my hidden sheets I do not necessarily no the name of the new sheet because of duplicates etc.

I do not no the index number of the sheet because when I put it after sheets.count it goes after the last visible sheet.

I would love to simply know the codename of the new sheet but again there can be "holes" in the codename numbering because of deletions.

How can I copy a sheet and know its name, codename and index afterward?

--

Dave Peterson