The VBA method for inserting a row in previous versions of Excel generates a run-time error '1004' Application-defined or object-defined error. Has the code model changed

Here's the sample code that will generate the error - It comes straint from KB Article - 291305 for Excel 2000 or Excel 9

Sub Insert_Rows_Loop(
Dim Current Sheet as Objec

' Loop through all selected sheet
For Each CurrentSheet in ActiveWindow.Selected Sheet
' Insert 5 rows at top of each shee
CurrentSheet.Range("a1:a5").EntireRow.Insert '*******This row generates the erro
Next CurrentShee
End Su

Any help you could provide would be most appreciated

I tested this in Excel 2000, Excel 2002, and Excel 2003. No error occurred

Regards
Edwin Ta

http://www.vonixx.co


----- stonequest wrote: ----

The VBA method for inserting a row in previous versions of Excel generates a run-time error '1004' Application-defined or object-defined error. Has the code model changed

Here's the sample code that will generate the error - It comes straint from KB Article - 291305 for Excel 2000 or Excel 9

Sub Insert_Rows_Loop(
Dim Current Sheet as Objec

' Loop through all selected sheet
For Each CurrentSheet in ActiveWindow.Selected Sheet
' Insert 5 rows at top of each shee
CurrentSheet.Range("a1:a5").EntireRow.Insert '*******This row generates the erro
Next CurrentShee
End Su

Any help you could provide would be most appreciated

žË¥¶w_


This is what i use on Excel 97



Sub Insert_Rows_Loop()
Dim CurrentSheet As Object

' Loop through all selected sheets
For Each CurrentSheet In Worksheets
' Insert 5 rows at top of each sheet
CurrentSheet.Rows("1:5").EntireRow.Insert
Next CurrentSheet
End Su

--
Message posted from http://www.ExcelForum.com

Is one of the sheets a chart perchance. Inserting rows, better to use the
Worksheets collection, but for selected sheets you need another tack

Sub Insert_Rows_Loop()
Dim CurrentSheet As Object

' Loop through all selected sheets
For Each CurrentSheet In ActiveWindow.SelectedSheets
' Insert 5 rows at top of each sheet
If CurrentSheet.Type = xlWorksheet Then
CurrentSheet.Range("a1:a5").EntireRow.Insert
End If
Next CurrentSheet
End Sub

Test that it is a worksheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"stonequest" wrote in message
...
The VBA method for inserting a row in previous versions of Excel generates
a run-time error '1004' Application-defined or object-defined error. Has
the code model changed?

Here's the sample code that will generate the error - It comes straint
from KB Article - 291305 for Excel 2000 or Excel 98

Sub Insert_Rows_Loop()
Dim Current Sheet as Object

' Loop through all selected sheets
For Each CurrentSheet in ActiveWindow.Selected Sheets
' Insert 5 rows at top of each sheet
CurrentSheet.Range("a1:a5").EntireRow.Insert
'*******This row generates the error
Next CurrentSheet
End Sub

Any help you could provide would be most appreciated!