Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error
Here is the code:
Dim A As String A = Range("J3").Value ActiveSheet.Select ActiveSheet.Copy , Befo=Sheets(1) ActiveSheet.Select Active.Sheet.Name = A Range("F5").Select The value in J3 is 10-02 (formatted as text using =TEXT(F4, "MM-DD"). It runs fine until the line "Active.Sheet.Name = A" and then stops with a 424 error "Object Required". Up to this point the new spreadsheet has been created but the tab name is still a copy of the old one. It is supposed to use the text in cell J3... Lastly I would like the cell F5 in the new worksheet to be the current date. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Create New Worksheet and Reference Cell in Old Worksheet
Try changing
Active.Sheet.Name = A to ActiveSheet.Name = A Vaya con Dios, Chuck, CABGx3 " wrote: Here is the code: Dim A As String A = Range("J3").Value ActiveSheet.Select ActiveSheet.Copy , Befo=Sheets(1) ActiveSheet.Select Active.Sheet.Name = A Range("F5").Select The value in J3 is 10-02 (formatted as text using =TEXT(F4, "MM-DD"). It runs fine until the line "Active.Sheet.Name = A" and then stops with a 424 error "Object Required". Up to this point the new spreadsheet has been created but the tab name is still a copy of the old one. It is supposed to use the text in cell J3... Lastly I would like the cell F5 in the new worksheet to be the current date. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Create New Worksheet and Reference Cell in Old Worksh
Sorry, didn't finish reading the whole post..........
Sub testme() Dim A As String A = Range("J3").Value ActiveSheet.Select ActiveSheet.Copy , Befo=Sheets(1) ActiveSheet.Select ActiveSheet.Name = A Range("F5").Select Range("f5").Value = Date End Sub This will set the date in F5 as current at the time the macro was run. Vaya con Dios, Chuck, CABGx3 "CLR" wrote: Try changing Active.Sheet.Name = A to ActiveSheet.Name = A Vaya con Dios, Chuck, CABGx3 " wrote: Here is the code: Dim A As String A = Range("J3").Value ActiveSheet.Select ActiveSheet.Copy , Befo=Sheets(1) ActiveSheet.Select Active.Sheet.Name = A Range("F5").Select The value in J3 is 10-02 (formatted as text using =TEXT(F4, "MM-DD"). It runs fine until the line "Active.Sheet.Name = A" and then stops with a 424 error "Object Required". Up to this point the new spreadsheet has been created but the tab name is still a copy of the old one. It is supposed to use the text in cell J3... Lastly I would like the cell F5 in the new worksheet to be the current date. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Create New Worksheet and Reference Cell in Old Worksh
Thanks Chuck! Working smooth as silk now....
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Create New Worksheet and Reference Cell in Old Worksh
Happy to help........thanks for the feedback.
Vaya con Dios, Chuck, CABGx3 " wrote: Thanks Chuck! Working smooth as silk now.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
Using cell value to reference external worksheet? | Excel Worksheet Functions | |||
How to use a cell value to reference a worksheet name | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |