Programmatically inserting a row with Excel 2003

S

stonequest

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
 
M

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
 
M

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
 
E

Edwin Tam

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

Regards
Edwin Ta
[email protected]
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
 
B

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)

stonequest said:
The VBA method for inserting a row in previous versions of Excel generates
Click to expand...
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
Click to expand...
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
Click to expand...
'*******This row generates the error
 
Top