Programmatically inserting a row with Excel 2003

[Guest]

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

 

[mudraker]

žË¥¶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

 

[mudraker]

žË¥¶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

 

[Guest]

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

Regards
Edwin Ta
(e-mail address removed)
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

 

[Bob Phillips]

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)

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