Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using sheet code name
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using sheet code name
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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using sheet code name
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using sheet code name
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Programmatically determining CODE NAME for sheet based upon Sheet | Excel Programming | |||
Sheet Code Module: copying code to | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming | |||
unprotect sheet in code and make sheet visible | Excel Programming |