VBS-Command Control Button-Add row below pasted content maintaining format of destination row

[nathanzumbaugh]

Good Morning
I would love to be able to click the button in C8 on "gencon" sheet to have paste under General Conditions on "Summary" sheet and have it add a row below the pasted content that is the same format as the destination row. The command control button code is fully functional but not the "add row and keep formatting" part.

Goal is to be able to click many of the buttons in column C on "gencon" tab and not have them run below the subsequent headers on "Summary" sheet. This will be a problem is more than 1 is clicked in its current state.

below is the current functional code to make it paste, without the added necessity mentioned above.

---
Private Sub CommandButton5_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("gencon")
Set pasteSheet = Worksheets("Summary")

copySheet.Range("d8:k8").Copy
If pasteSheet.Range("gcsum").Offset(1, 0) <> "" Then
pasteSheet.Range("gcsum").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Else
pasteSheet.Range("gcsum").Offset(1, 0).PasteSpecial xlPasteValues
End If

copySheet.Range("d8:k8").ClearContents

Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub

 

[Micron]

Not sure which of those lines that contain the format of your copied range but I think the answer is to paste again, but paste only the formatting. Maybe like
pasteSheet.Range("gcsum").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
and then
pasteSheet.Range("gcsum").End(xlDown).Offset(1, 0).PasteSpecial xlFormats

XlPasteType enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

Please use code tags (click VBA button on posting toolbar and paste code between the resulting tags) to maintain indentation and readability.

 

[nathanzumbaugh]

row 123 of the destination sheet "summary" has the formatting i would like to maintain when adding a row below it, from the source paste range "gencon"

Ideally, i would like row 124 on "summary" to be the same as 123 is in the image, and the row with Safety in it to be bumped down when the command click button on "gencon" is clicked.