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"

