Need help with VBA to copy formulas and formatting to new row

[sparkytech]

I need help with VBA to copy formulas and formatting to new row. The code below inserts (5) new rows at the top of my table. How can I make this copy the formatting, conditional formatting, and formulas from the existing row below?

Sub InsertFiveRows(control As IRibbonControl)
Application.ScreenUpdating = False
Dim i As Integer
For i = 1 To 5
Sheet1.Cells(5, 1).EntireRow.Insert
Next i
Application.ScreenUpdating = True
End Sub

 

[Jon Peltier]

Here, try this.

Sub InsertFiveRows(control As IRibbonControl)
    Application.ScreenUpdating = False
    Sheet1.Rows("5:5").Copy
    Sheet1.Rows("5:9").Insert Shift:=xlDown
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[sparkytech]

Here, try this.

Sub InsertFiveRows(control As IRibbonControl)
    Application.ScreenUpdating = False
    Sheet1.Rows("5:5").Copy
    Sheet1.Rows("5:9").Insert Shift:=xlDown
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Thanks! Am I doing something wrong? I get a Run-time error 1004: "This won't work because it would move cells in a table on your worksheet." This error occurs on the Insert Shift command.

 

[Jon Peltier]

I don't know why your initial code would work and mine would not.

Is your data in an actual Ctrl+T Table? What does the data look like?

 

[sparkytech]

I don't know why your initial code would work and mine would not.

Is your data in an actual Ctrl+T Table? What does the data look like?

I'm not sure why either. The data is mostly text and dates. There are 2 cells with formulas, and some with conditional formatting. The table didn't exist in the original spreadsheet, and was created with Styles>Format as Table.

 

[sparkytech]

I'm still learning VBA, so this is just a thought... what if it copies existing row 5, and pastes only formulas and formatting one row above (in row 5, everything else shifts down)?

 

[Jon Peltier]

Capital-T Tables are created using Format as Table, Insert Table, or Ctrl+T.

Inserting a formula in a Table will insert that formula into every cell of that column of a Table. It is possible to mess up column formulas, though, especially if you'd written some formulas before converting a range into a Table. You can usually fix this by clearing the data rows (not header) of the affected column, then entering the formula in the top row of the Table column.

Column formulas, formatting, conditional formatting, or data validation applied to a Table column will extend to new rows inserted within or appended to the Table. Again, it is possible to mess up column conditional formatting or data validation.

So when you insert several rows to your worksheet, which inserts rows within the Table, your formatting and formulas should be included in the inserted parts of the Table.

You can also insert Table rows within the Table, leaving the rest of the worksheet unchanged.