VBA error

[Guest]

I get an error, "Run-time error '1004': Application-defined or object-defined
error" when I run the following macro:

Sub Sheet_Names()
Dim ws As Worksheet
With Worksheets.Add
.Name = "Sheet Names"
.Move before:=Worksheets(1)
End With
Sheets("Sheet Names").Activate
Range("a1").Activate
For Each ws In Worksheets
If ws.Name <> "Sheet Names" Then
ActiveCell.Formula = ws.Name
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub

What does this error mean and how can I resolve it? The macro is supposed
to create a list of tab names in the workbook.

Thanks,

Dave

 

[PCLIVE]

Your code works for me. Where does it fail?

 

[Ron de Bruin]

Hi dave

Copy the macro in a normal module and not in a sheet module

 

[Guest]

this is a problem which occassionally shows up when you copy sheets often
without saving the workbook
try saving thew workbook and try running the macro again. It works fine for
me 2003

 

[Guest]

Thanks. So, basic question: how do you know when a block of code should be
contained in its own module?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

Hi dave

Copy the macro in a normal module and not in a sheet module

 

[Ron de Bruin]

Hi Dave

Always copy macros in a normal module
Sheet modules are for events for the sheet or controls on the sheet.

See this page
http://www.cpearson.com/excel/codemods.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Thanks. So, basic question: how do you know when a block of code should be
contained in its own module?

Dave

 

[Guest]

Exactly the info I was looking for, thanks.

Dave