How can I use VBA to copy values in a column given a condition in another column into a different sheet with no gaps in the data?

Viewed 24

I have a table with 15 columns. I'm trying to copy and paste 3 of the columns into another sheet and then duplicate that info below itself in the destination sheet a specified number of times based on the number layers in the test I'm conducting. I'm having trouble with the code finding the bottom row of the copied data and I haven't found code that will eliminate the gaps in the data.

this is my first post so I cant add pictures yet.

here's my current code for the button that is supposed to populate the destination sheet:

Private Sub PRTButton_Click()
    CopyInfo
    PasteInfo
End Sub

Sub CopyInfo()
    Dim aLastRow As Long
    aLastRow = Sheets("Test Ammo").Cells(Rows.Count, 1).End(xlUp).Row
    'ammo description
    Sheets("PRT Endurance").Range("B6:B" & aLastRow - 1).Value = Sheets("Test Ammo").Range("O64:O113" & aLastRow).Value
    'Ammo Spec
    Sheets("PRT Endurance").Range("C6:C" & aLastRow - 1).Value = Sheets("Test Ammo").Range("C64:c113" & aLastRow).Value
    'QTY Shot
    Sheets("PRT Endurance").Range("D6:D" & aLastRow - 1).Value = Sheets("Test Ammo").Range("N64:N113" & aLastRow).Value
End Sub
Sub PasteInfo()
    Dim LRow As Long
    With ActiveSheet
    LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    Range("B6:D" & LRow).Copy
    MsgBox "Info is already in clipboard. Select your layers from the dropdown and paste at the bottom of the copied info for each layer."
End Sub

I know i haven't told the code to only copy the values with a number in the M column and paste them into the destination sheet. for reference, I only want to copy the rows that have a value in the M column in my origin sheet. I'm not sure how to tell the code to select the 3 row dynamic range in the destination sheet and paste it a certain amount of times below it.

If I missed any explanations or there's any confusion, let me know and I'll try to clarify.

Origin table

Table after pressing "Populate ammo info"

0 Answers
Related