(Solved) – VBA Code to paste the same values into a dynamic range of rows?

  • by
(solved)-–-vba-code-to-paste-the-same-values-into-a-dynamic-range-of-rows?

I’m currently trying to make a spreadsheet where in one tab I can input info for a project as well as the projected hours for each staff member working on it, then by activating a macro it will paste this info into a separate sheet that houses all my data. The issue I’m running into is that in the Data sheet I need the project info repeated for each staff member. The code I have is:

'
' Macro1 Macro
' Trying to move stuff
'
Set wsCopy = Worksheets("Input")
Set wsDest = Worksheets("Projected Hours")
DestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
wsCopy.Range("A8:A20").Copy
    wsDest.Range("E" & DestLastRow).PasteSpecial Paste:=xlPasteValues
wsCopy.Range("B8:B20").Copy
    wsDest.Range("L" & DestLastRow).PasteSpecial Paste:=xlPasteValues
wsCopy.Range("C8:C20").Copy
    wsDest.Range("N" & DestLastRow).PasteSpecial Paste:=xlPasteValues
wsCopy.Range("D8:O20").Copy
    wsDest.Range("Q" & DestLastRow).PasteSpecial Paste:=xlPasteValues

NewLastRow = wsDest.Cells(wsDest.Rows.Count, "E").End(xlUp).Offset(1).Row

wsCopy.Range("B5:D5").Copy
    wsDest.Range(Cells(B, DestLastRow), Cells(B, NewLastRow)).PasteSpecial Paste:=xlPasteValues
wsCopy.Range("E5:J5").Copy
    wsDest.Range(Cells(F, DestLastRow), Cells(F, NewLastRow)).PasteSpecial Paste:=xlPasteValues

Where the first block of code finds the first empty row in the Data sheet and pastes all of the staff info in. Then I define a new last row (NewLastRow), and paste in the project info on every row between the two. I’m prompted to debug on the first paste into this new range and am not sure what to do. Also I think NewLastRow is defined as being a row too low, so a way to fix that would also help. Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *