Macro to repeat x amount of times - next row

[Closed]
Report
Posts
1
Registration date
Tuesday August 30, 2016
Status
Member
Last seen
August 30, 2016
-
I'm trying to wrote a macro which writes a csv from lines of data. Currently it roughly runs:

Copy B3:L3
Paste in A2 (new page)
Copy A22
Paste in A1 (new sheet)
Copy B4:L4
Paste in A2 (new page)
Copy A22
Paste in A2 (new sheet)
Copy B5:L5
Paste in A2 (new page)
Copy A22
Paste in A3 (new sheet)

I need to find a way for the macro to repeat - the changing factor will be the copied range (B3:L3 repeated down either 50 times, or until there is a blank row) and the final paste (either 50 times or in the next blank row)

Does anyone know the workaround for this?
(I've included two instances of this code below, aside from copy and pasting and changing the cell manually i'm stumped)


Windows("RJO PAYMENTS DATABASE.xlsm").Activate
Sheets("DATA").Select
Range("B10:L10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PAYMENTUPLOAD").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A22").Select
Application.CutCopyMode = False
Selection.Copy
Windows("RBSPAYMENT-" & Format(Now(), "yyyymmddhhnn") & ".csv").Activate
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("RJO PAYMENTS DATABASE.xlsm").Activate
Sheets("DATA").Select
Range("B11:L11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PAYMENTUPLOAD").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A22").Select
Application.CutCopyMode = False
Selection.Copy
Windows("RBSPAYMENT-" & Format(Now(), "yyyymmddhhnn") & ".csv").Activate
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False