Excel 2007 Button to add more rows to table

[duffers123]

Hello there,

This is my 1st time trying to code in VBA. I have a table (with formatting) with 8 columns on sheet 1 of my workbook in Excel 2007. I have created a button that when clicked it adds 37 rows to my table (just enough to add another page for printing). Below is my code

Sub Button1_Click()If Not IsEmpty(ActiveCell.Value) Then


For i = 1 To 37


Selection.EntireRow.Insert
Range("B" & Selection.Row).Formula = "=R[-1]C+1"
Range("B" & Selection.Row + 1).Formula = "=R[-1]C+1"


Next


Else
MsgBox "Please place the cursor within the table"
End If


End Sub

Is it possible for the new cells to have the same formatting as the rest of the table?

Another issue I am having is that I want sheet 2 to display the same table just with three columns removed. I cannot get the table in sheet 2 to add new rows after clicking the button on sheet 1.

Any help would be much appreciated... Thanks

 

[VBA Geek]

Maybe this? It would add 37 new rows from A2 down

Sheets("Sheet1").Range("A2:A38").EntireRow.Insert xlDown, True
Sheets("sheet2").Range("A2:A38").EntireRow.Insert xlDown, True

 

[duffers123]

Maybe this? It would add 37 new rows from A2 down

Sheets("Sheet1").Range("A2:A38").EntireRow.Insert xlDown, True
Sheets("sheet2").Range("A2:A38").EntireRow.Insert xlDown, True

Thanks for your response. Unfortunately that just shifts my table down by 37 rows and leaves blank cells above it. The purpose of the button is to add 37 new rows each time it is pressed so that the user can keep adding more columns to the table.

 

[VBA Geek]

So it should add 37 new rows starting from the end of your table?

 

[duffers123]

Basically I have a table with 8 columns with headers in row 9. The user should be able to put text into column C and type numbers into columns D & F so that columns E, G & H can update (from using formulae). At the end of the table on row 31 there is a TOTAL number in column H. I want the button to add 37 new rows below row 30 and above row 31. It is important that the cells in the new rows in columns E,G & H all contain the same formulae to keep a running total.

 

[VBA Geek]

Range("A31:A68").EntireRow.Insert xlUp, True
Range("E30:H30").AutoFill Range(Range("E30:H30"), Range("E68:H68"))

this would work?

 

[duffers123]

Thanks I didn't think to use AutoFill. Instead of Range(Range("E30:H30"), Range("E68:H68")) How can this be programmed to fill to the bottom like using Selection.End(xldown)?

 

[VBA Geek]

Range("E30:H30").AutoFill Range(Range("E30:H30"), Range("E30:H30").End(xlDown))