VBA Insert rows

[candacem]

Hi Everyone,

I have been working on a string and having issues. I have the basic set out, but I want to add in where the rows should be inserted, not at cell A1, but starting at cell A8.

Sub MyInsertRows1()

Application.DisplayAlerts = False

Dim i As Integer

For i = 1 To Range("d1").Value
Rows(1).Insert
Next i

Application.DisplayAlerts = True


End Sub

I'm still learning VBA so please be kind!

 

[Scott T]

Try

Sub MyInsertRows1()
Application.DisplayAlerts = False
 Dim i As Integer
 For i = 1 To Range("d1").Value
 Range("A8").EntireRow.Insert
 Next i
 Application.DisplayAlerts = True

 End Sub

 

[candacem]

I get an error with that one

Run-time error '1004' : Application-defined or object-defined error

 

[JackDanIce]

Try:

Sub InsertRows()

    If Range("D1").Value > 0 Then Range("A8").Resize(Range("D1").Value).EntireRow.Insert
    
End Sub

 

[candacem]

Receive an error on both options, same code as listed above

 

[JackDanIce]

On what line do you receive that error?

 

[candacem]

Insert method of range class failed

 

[JackDanIce]

That's the error message, not what line of code it occurs on.

Both suggestions run fine for me, without that error message.

 

[Fluff]

Is the sheet protected?

 

[candacem]

That fixed it, but I didn't realize that I needed to be "below" A8 (A8 is a header to the Account Number) - As below. My end goal is to have a button to update the account numbers (replacing the text that is currently appear as below) based off of the cost centre. So step one is to get the macro to insert the appropriate amount of rows to accommodate the second step of the formula to look up the account numbers associated with the cost centre...I have that gives a bit more clarity to the end goal.

[TABLE="width: 272"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Acct Number[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]Salaries[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Benefits[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Building Expense - Non-Staff[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Rent[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Advertising & Promotion[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Field Support Functions[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]