Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Copy Worksheet Problem | Excel Discussion (Misc queries) | |||
Copy Worksheet wont copy objects. | Excel Worksheet Functions | |||
Unable to Insert Worksheet/Move and copy worksheet Excel 2003 | Excel Worksheet Functions | |||
Worksheet copy problem - local names | Excel Discussion (Misc queries) | |||
worksheet copy problem | Excel Programming |