Run time 1004 error in VBA

[Kimberly]

I am a very novice VBA user.
I am making schedule worksheets for 3 different shifts. I want pages to name themselves based on data in a cell.
Using View Code on a page tab, I have this entered:

Private Sub Auto_Open()
ActiveSheet.Name = ActiveSheet.Range("IA2").Value
End Sub

When building, I tried saving this in Workbook in the Project-VBA Project window but it wouldn't run when I opened the notebook. Somehow I figured out how to save it as a module under that workbook. And it ran beautifully...every time.

Then I copied the Workbook for the other two shifts. In the Project-VBA Project window, it showed the module under each project.

Then when I tried any of the workbooks, I got Run-Time Error "1004" Application-defined or object-defined error.

I have tried saving the formula in a page, in the workbook, taking it out and reentering it. No luck...same error.

Can anyone help?
Kimberly

[CodeMakr]

Have you tried to save the module in your personal.xls vbaproject folder and linking your macro to an excel toolbar button? The toolbar button would be visible no matter which workbook you open.

[Simon Lloyd]

The Private Sub Auto_Open is for a workbook open event, ideally you want to use the Worksheets Activate, go to the worksheet code module choose worksheet from the left drop down and the Activate from the right hand, then use your statement like this [VBA]Private Sub Worksheet_Activate()
ActiveSheet.Name = Range("A1").Value
End Sub[/VBA]Regards,
Simon

[vonpookie]

Just a note, but this was cross-posted:
http://www.mrexcel.com/board2/viewtopic.php?t=247145

[lucas]

Hi Kimberly,
Just a note about cross-posting. Please follow this link and read it for an explanation of cross-posting
http://www.excelguru.ca/node/7

Simon gave you the best solution to your problem....see attached and look at the code for sheet1(right click on the tab at the bottom of the page and select "view code")

There are other ways to do this including formula's

[johnske]

If you want this to apply to every sheet, use this variation in the ThisWorkbook code module [VBA]Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next '< error=another sheet has same name, cell is empty or illegal character, is chart, etc
ActiveSheet.Name = Range("A1").Value
End Sub[/VBA]