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