VBA to copy data from one sheet and paste to blank row of another

[srj1359]

Hi there! I'm needing to copy a range of data from one sheet (Pricing) to another sheet (Multiple Participants) and I need it to paste to the first blank row each time I push the macro button.

For example: I choose customer A in the pricing tab, push the button, it copies the range and pastes to the multiple participants tab. I then go back to pricing and choose customer B, push the button, it copies the range and pastes in multiple participants below customer A data.

Here is the code I currently have. All ranges that have a 9 (B9, I9, K9) are because row 9 is the first blank row under my headers. So I need to change that range to be the first blank row in each of those columns.

Sheets("Pricing Sheet").Select
Range("B22:G22").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Multiple Participants").Select
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Pricing Sheet").Select
Range("J22:K22").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Multiple Participants").Select
Range("I9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Pricing Sheet").Select
Range("L22:M22").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Multiple Participants").Select
Range("K9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'get last row of Pricing
Dim lrowCQ As Integer
lrowCQ = Range("B" & Rows.Count).End(xlUp).End(xlUp).End(xlDown).Row

Any assistance you can provide is greatly appreciated. Thank you!

 

[DanteAmor]

You are incredible! Thank you so much!!!

I'm glad to help you. Thanks for the feedback.

For the new theme, create a new thread.