Insert New Row including the formating

[xluser26]

Hope Everyone is well,

I'm trying to do something very simple - I am using the code below to add a new row on the top row by clicking a Command Button. I'm having a hard time also including the format, some cells on the row have to be a specific format (Text, Date, Currency, Center aligned, etc.) Note: I'm using an auto expand table.

This is the code i am using:

Private Sub CommandButton1_Click()
Dim mySheets
Dim i As Long

mySheets = Array("Sheet1")

For i = LBound(mySheets) To UBound(mySheets)
With Sheets(mySheets(i))
.Range("B8").EntireRow.Insert shift:=x1Down
.Range("B8:D8").Borders.Weight = xlThin
End With
Next i
End Sub

----------------------

I got this code from a search to add a new row online, so I am trying to build off of it.

Thank you for all the help.

xluser26

 

[Michael M]

Maybe this

Private Sub CommandButton1_Click()
With Rows("8")
    .Copy
    .Insert
    .ClearContents
End With
End Sub

 

[xluser26]

Appreciate it Michael.

I have a table ("my auto expand table), which in order for the above code to work, i have to convert it back to a range table, which then doesn't allow a vlookup formula to work anymore because it doesn't copy it to the new row.

The VLOOKUP formula is =VLOOKUP($B4,UNITBRSIZE_TABLE,2,FALSE)
its in the column over.

Again thank you,

xluser26