VBA Code to insert rows receiving an error

[crittenberry]

Hello,

I am using a macro that imports data from a third party source via a txt file. The data is then split into new worksheets based on an ID number. (The number of spreadsheets created will vary per run). This part of the macro runs perfectly. The issue I am having is when the macro tries to add new rows. This is what I am currently using for that part:


Invoice = Sheets.Count
For x = 3 To Invoice
Sheets(x).Select
Range("A1").Select
Rows("1:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next x




The first new worksheet goes fine; however, when it goes to the next sheet the macro returns the following error:

"Microsoft Excel can't insert new cells because it would push non-empty cells off the end of the worksheet. These cells might appear empty but have blank values, some formatting, or a formula. Delete enough rows or columns to make room for what you want to insert and then try again.


The rows are being added for formatting.

Any help would be greatly appreciated.

 

[Fluff]

If you select one the sheets that causing a problem & run this

Sub chk()
MsgBox ActiveSheet.UsedRange.Rows.Count
End Sub

What does the message box say?

 

[crittenberry]

The one I ran it on says 745.

I also just noticed that if I manually try to insert rows after the error, the option is "insert copied cells" instead of simply "insert"

 

[Fluff]

In that case try

Dim x As Long
Application.CutCopyMode = False
For x = 3 To Worksheets.Count
   With Sheets(x)
      .Rows("1:5").Insert
   End With
Next x

 

[crittenberry]

That worked perfectly. Thank you for the help!

 

[Fluff]

You're welcome