Nope... A workbook object that is created at run time can not refernce sheets
by their code name whether it is active or not... The best you could do would
be to use code that traversed all of the sheets in the workbook and look for
a sheet with a matching code name. Something like this.

Sub Test()
Dim wbkNew As Workbook
Dim wks As Worksheet

Set wbkNew = Workbooks.Add
For Each wks In wbkNew.Worksheets
If wks.CodeName = "Sheet3" Then MsgBox "Tada"
Next wks
End Sub

You could also make that into a function that took a Workbook object and a
codename string and returned the matching worksheet (if one existed) which
would be a pretty easy work around for your code.
--
HTH...

Jim Thomlinson


"ll" wrote:

On May 2, 10:25 am, "Jim Rech" wrote:
I've been modifying my existing, working code to use sheet code names

I didn't examine your code but one caveat. The limitation of using code
names is that they was only visible from within the project they belong to.
So code in one workbook cannot use a codename of a sheet in another.

--
Jim"ll" wrote in message

ps.com...
| Hi,
| I've been modifying my existing, working code to use sheet code names,
| rather than sheet label names, and I am getting the "method 'select'
| of object '_worksheet' failed".
| In using the codenames, are there perhaps other areas of my code that
| need adjustment?
| Thanks,
| Louis
|
| -----
| Sub timesheetGenerate()
| Dim fillamt As Long
| Dim origDate As Date
|
| Dim LOldWb As Workbook
| Dim LNewWb As Workbook
| Dim x As Name
|
| 'Open existing timesheet
| Set LOldWb = Workbooks.Open(Filename:=UserForm1.TextBox1.Value)
|
| 'Add new workbook
| Set LNewWb = Workbooks.Add
|
| 'Get data from Sheet 3 (dates) of existing timesheet
| LOldWb.Activate
| Sheet3.Visible = xlSheetVisible
| Sheet3.Select
|
|
| LNewWb.Activate
| Sheet3.Select
| Cells(1, 1).Select
| ActiveCell.Value = UserForm1.TextBox4.Value
|
| '\\\\Populate Column A with date range
| fillamt = UserForm1.TextBox6.Value
| Selection.AutoFill Destination:=Range("A1:A" & fillamt),
| Type:=xlFillSeries
|
|
|
| 'Copy sheet one of existing timesheet
| LOldWb.Activate
| Sheet1.Select
| Cells.Select
|
| '///replace formula with string
| Selection.Replace What:="=", Replacement:="$$$$$=", LookAt:=xlPart, _
| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
| _
| ReplaceFormat:=False
| '///copy all
| Selection.Copy
|
|
| 'Paste sheet one to new timesheet
| LNewWb.Activate
| Sheet1.Select
| '///paste all (values)
| Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
| '///replace string with formula
| Cells.Select
| Selection.Replace What:="$$$$$=", Replacement:="=",
| LookAt:=xlPart, _
| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
| _
| ReplaceFormat:=False
|
|
|
| 'Copy sheet two of existing timesheet
| LOldWb.Activate
| Sheet2.Select
| Cells.Select
| '///replace formula with string
| Selection.Replace What:="=", Replacement:="$$$$$=", LookAt:=xlPart, _
| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
| _
| ReplaceFormat:=False
| '///copy all
| Selection.Copy
|
|
|
| 'Paste sheet two to new timesheet
| LNewWb.Activate
| Sheet2.Select
| '///paste all (values)
| Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
| '///replace string with formula
| Cells.Select
| Selection.Replace What:="$$$$$=", Replacement:="=",
| LookAt:=xlPart, _
| SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
| _
| ReplaceFormat:=False
|
|
| Sheet1.Select
|
|
| ' Loop through all of the defined names in the active
| ' workbook.
| For Each x In LOldWb.Names
| ' Add each defined name from the active workbook to
| ' the target workbook ("Book2.xls" or "Book2.xlsm").
| ' "x.value" refers to the cell references the
| ' defined name points to.
| LNewWb.Names.Add Name:=x.Name, _
| RefersTo:=x.Value
| Next x
|
| ActiveWorkbook.SaveAs Filename:=UserForm1.TextBox2.Value
|
|
| '\\\\
| ActiveWorkbook.Close
| '\\\\
|
| '\\\\
| LOldWb.Activate
|
| ActiveWorkbook.Close
|
| End Sub
|



Thanks Jim,
If another workbook is activated, can the codename of one of its
sheets be referenced?

-Louis