Loop through first row of each named range in one code block

Question

So I've had to write two almost identical code blocks to loop through my two named ranges. However, I know that the named ranges will always be the same size and shape as each other and even start in the same column (different rows) and they also need to be pasted into two columns next to each other so I feel like this should be possible in one code block but can't even think how to start attacking it. E.g. Cash Payments Monthly is in array A10:D20 and P&L Payments Monthly is in array A40:D50.

Anyone got any ideas, please and thank you?

For Each Row In Range(Names("Cash_Payments_Monthly")).Rows

LastRow = wsDashData.Cells(Rows.Count, 14).End(xlUp).Row

Row.Copy

wsDashData.Range("n" & LastRow + 1).PasteSpecial _
Paste:=xlPasteValues, _
Transpose:=True

Next Row




For Each Row In Range(Names("PL_Payments_Monthly")).Rows

LastRow = wsDashData.Cells(Rows.Count, 15).End(xlUp).Row

Row.Copy

wsDashData.Range("o" & LastRow + 1).PasteSpecial _
Paste:=xlPasteValues, _
Transpose:=True

Next Row

Answer 1

Assuming you have other named ranges in your workbook, you should start by creating a whitelist array of named ranges that you WOULD like to search, then iterate through that array, embedding a single copy of your existing code in that loop...

Dim myranges()
Dim c As Integer 'counter

myranges = Array("Cash_Payments_Monthly", "PL_Payments_Monthly")


For c = 0 To UBound(myranges)
    For Each Row In Range(myranges(c)).Rows
    ...the rest of your code, but just one instance of it :-) ...
Next c