Run-Time Error 1004

[Kimberly2]

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 Autpen()
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

 

[MacroAlan]

What is the value in IA2? If it has illegal characters such as slashes "/" it will produce an error. If these are dates, you may have to format them within slashes such as 20061208

 

[Kimberly2]

All plain letters - no special digits. And there is something in the cell.

 

[CBasicAsslember]

Try this.

Sub Auto_Open()
Dim sTest As String
Stop
sTest = Sheet1.Range("A1").Value
ActiveSheet.Name = sTest

End Sub

When it stops step through the code (F8) to make sure you have a variable in sTest.

 

[SkipVought]

Hi,

"I am making schedule worksheets for 3 different shifts. I want pages to name themselves based on data in a cell."

Why would you do this EVERY TIME you open the workbook?

Is it the same cell on each worksheet?

Sub NameAllSheets()
  Dim ws as worksheet
  for each ws in worksheets
    with ws 
      .name = .[IA2].value 
    end with
  next
End Sub

If you needed to run this when the workbook opened, then use the Workbook_Open event. Activate the ThisWorkbook object in the Project explorer and select the Workbook in the Object dropdown in the Code Window.


Skip,

_[red][/red]