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

nathanzumbaugh

New Member
Joined
Jan 7, 2009
Messages
9
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

1715778353261.png

1715778423071.png
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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


Please use code tags (click VBA button on posting toolbar and paste code between the resulting tags) to maintain indentation and readability.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top