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]

How about

Sub CopyData1()
  Dim lr As Long, lr2 As Long
  With Sheets("Pricing Sheet")
    lr = .Range("B" & Rows.Count).End(3).Row
    .Range("B22:G" & lr).Copy
    With Sheets("Multiple Participants")
      lr2 = .Range("B" & Rows.Count).End(3).Row + 1
      .Range("B" & lr2).PasteSpecial Paste:=xlPasteValues
    End With
    .Range("J22:M" & lr).Copy
    Sheets("Multiple Participants").Range("I" & lr2).PasteSpecial Paste:=xlPasteValues
  End With
End Sub

Or this

Sub CopyData2()
  Dim lr As Long
  With Sheets("Pricing Sheet")
    lr = .Range("B" & Rows.Count).End(3).Row
    Sheets("Multiple Participants").Range("B" & Rows.Count).End(3)(2).Resize(lr - 21, 6).Value = .Range("B22:G" & lr).Value
    Sheets("Multiple Participants").Range("I" & Rows.Count).End(3)(2).Resize(lr - 21, 4).Value = .Range("J22:M" & lr).Value
  End With
End Sub

 

[vaskov17]

Sub copy()
    Dim wkPricing As Worksheet
    Dim wkParticipants As Worksheet
    Dim lr As Long
    
    Set wkPricing = ThisWorkbook.Worksheets("Pricing Sheet")
    Set wkParticipants = ThisWorkbook.Worksheets("Multiple Participants")
    
    lr = wkParticipants.Cells(wkParticipants.Rows.Count, "B").End(xlUp).Row + 1
    
    wkPricing.Range("B22:G22", wkPricing.Range("B22:G22").End(xlDown)).copy
    wkParticipants.Range("B" & lr).PasteSpecial xlPasteValues
    
    wkPricing.Range("J22:M22", wkPricing.Range("J22:M22").End(xlDown)).copy
    wkParticipants.Range("I" & lr).PasteSpecial xlPasteValues
End Sub

 

[srj1359]

Thank you for the responses! I tried your first option @DanteAmor and it worked! One final piece I'm hoping someone can maybe help me with...

This is the same workbook and same sheets (Pricing Sheet and Multiple Participants). Cell DEF15 (merged columns) in Pricing Sheet contains the customer name. Can I please get some help figuring out a way to copy and paste that name into the column A in first row under the headers, then autofill down the correct number of rows of data. And after going back to pricing sheet, picking a new customer, and clicking the button, it will copy and paste the data and the new customer name will transfer and autofill down.

Please let me know if I need to clarify at all.

Thank you again -- I appreciate your help SO MUCH!

 

[DanteAmor]

And after going back to pricing sheet, picking a new customer, and clicking the button

Do you want a cycle?
Repeat the steps for each customer name?
Exactly where is list of customer names: sheet, column, start row, end row?

 

[srj1359]

Attached is a screenshot. The customer name comes from a drop down list on row 15 of the Pricing sheet tab. What I want to make happen is:

1) Account owner comes and selects customer from drop down, then pushes "Update Customer" button
2) The data in the background will run and present them a list of what the customer owns and doesn't own, they choose whatever subjects, collections, etc. they want to include on a quote, then click Multiple Participants
3) That data exports to the Multiple Participants sheet, which you helped me achieve in the first part of my question.
4) The account owner can then go back to the Pricing Sheet, choose a new customer and repeat steps 1-3, until they have a quote with all the desired customers

Everything above work perfectly, I just need a way to distinguish which data is for what customer, so I'm wanting to take that account name, copy and paste as values and autofill to match the correct number of rows for data, then repeat again when new data is copied over after pushing the "Multiple Participants" button again.

I hope that all makes sense.

 

[DanteAmor]

3) That data exports to the Multiple Participants sheet, which you helped me achieve in the first part of my question.

At this point, that is, in the code that I put you, at that very moment do you want to put the customer name?

 

[srj1359]

@DanteAmor sure, if that's what you think is best/easiest

 

[DanteAmor]

Try this

Sub CopyData2()
  Dim lr As Long, lr2 As Long, sh2 As Worksheet
  Set sh2 = Sheets("Multiple Participants")
  With Sheets("Pricing Sheet")
    lr = .Range("B" & Rows.Count).End(3).Row
    lr2 = sh2.Range("B" & Rows.Count).End(3).Row + 1
    sh2.Range("B" & lr2).Resize(lr - 21, 6).Value = .Range("B22:G" & lr).Value
    sh2.Range("I" & lr2).Resize(lr - 21, 4).Value = .Range("J22:M" & lr).Value
    sh2.Range("A" & lr2).Resize(lr - 21, 1).Value = .Range("D15")
  End With
End Sub

 

[srj1359]

You are incredible! Thank you so much!!!

The only problem I'm encountering I think has to do with another part of the code I had, but not one from you. After the data is pasted in the Multiple Participants tab, I'm removing the "total" rows (my headers are in row 8) and the below code was what I used:

Range("B8:I8").Select
Selection.AutoFilter
ActiveSheet.Range("$B$8:$I$" & lrowCQ).AutoFilter Field:=1, Criteria1:= _
"=**Total*", Operator:=xlAnd
Range("$b$9:$I" & lrowCQ).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Selection.AutoFilter

I'm not sure if it's giving me a problem now because I have something in column A or if it's because the total row doesn't necessarily fall in row 9.