|
VBA error
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 befo=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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
VBA error
Your code works for me. Where does it fail?
"Dave F" wrote in message ... 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 befo=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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
VBA error
Hi dave
Copy the macro in a normal module and not in a sheet module -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave F" wrote in message ... 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 befo=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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
VBA error
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 "Dave F" wrote: 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 befo=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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
VBA error
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. "Ron de Bruin" wrote: Hi dave Copy the macro in a normal module and not in a sheet module -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave F" wrote in message ... 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 befo=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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
VBA error
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 "Dave F" wrote in message ... 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. "Ron de Bruin" wrote: Hi dave Copy the macro in a normal module and not in a sheet module -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave F" wrote in message ... 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 befo=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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
VBA error
Exactly the info I was looking for, thanks.
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Ron de Bruin" wrote: 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 "Dave F" wrote in message ... 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. "Ron de Bruin" wrote: Hi dave Copy the macro in a normal module and not in a sheet module -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave F" wrote in message ... 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 befo=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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
| All times are GMT +1. The time now is 11:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com