Help with VBA code for adding new rows

[SirSquiddly]

I have a button that adds 'x' amount of rows when desired number is entered. I feel this VBA is close and need help adjusting it. My data starts on row 5 and would like new blank (but containing formulas) added above the existing table. E,g,. pushes rows down.

A run-error '1004' pops up saying "this won't work because it would move cells in a table on your worksheet. I click debug and it highlights "Rng.Insert Shift:=x1Down"

Any help hugely appreciated​

[TABLE="width: 4217"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 4217"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OFF HIRE[/TD]
[TD]OFF HIRE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

​

Code:


Sub AddRows()


Const BaseRow As Long = 5 ' modify to suit


Dim x As String ' InputBox returns text if 'Type' isn't specified
Dim Rng As Range
Dim R As Long


x = InputBox("How many rows would you like to add?", "Insert Rows")
If x = "" Then Exit Sub
R = BaseRow + CInt(x) - 1


Rows(BaseRow).Copy 'Copy BaseRow
'specify range to insert new cells
Set Rng = Range(Cells(BaseRow, 1), Cells(R, 1))
Rng.Insert Shift:=x1Down


' insert the new rows BEFORE BaseRow
' to insert below BaseRow use Rng.Offset(BaseRow - R)
Set Rng = Rng.Offset(BaseRow - R - 1).Resize(Rng.Rows.Count, ActiveSheet.UsedRange.Columns.Count)
Rng.Select
On Error Resume Next
Rng.SpecialCells(xlCellTypeConstants).ClearContents
Application.CutCopyMode = False '
End Sub

 

[SirSquiddly]

I just created a new workbook with a table and it appears to be working. Thanks for everyones help. Life savers!

 

[tyija1995]

Glad it works now!

 

[SirSquiddly]

False alarm.

It works when my table does not have headers. When the headers are re-entered the same message as before occurs. Unfortunately, I require the table to be sorted by A-Z, etc. Any ideas on this?