Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default 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   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default 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   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default Macro to Create New Worksheet and Reference Cell in Old Worksh

Thanks Chuck! Working smooth as silk now....

  #5   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Lookup Data in two seperate Spreadsheets Padraig Excel Worksheet Functions 6 June 28th 06 03:05 PM
Using cell value to reference external worksheet? tsobiech Excel Worksheet Functions 2 March 6th 06 04:11 PM
How to use a cell value to reference a worksheet name S2 Excel Worksheet Functions 2 October 10th 05 03:02 PM
name of another worksheet in cell for reference Tom A Johnson Excel Worksheet Functions 2 November 12th 04 12:28 AM


All times are GMT +1. The time now is 07:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"