using sheet code name

[ll]

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

 

[Jim Rech]

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
| 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
|

 

[ll]

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.

--

| 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

 

[Jim Thomlinson]

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.