Copy and Paste Macro with variable length

[actuary78]

Hi there, i've searched the forum, but cannot quite work out how to do this.

I have the following copy and paste macro, which copies data into a monte carlo sim i've made, and then the results back to a data file

Is it possible to put this on a loop, where the data range rather than being a fixed number of rows e.g. 5 to 11, varies based on the cell value in column A of the row each loop starts in.

Example: So below on the first loop, the data range starts at BG5 but is the number of rows of the value in cell A5 (say value=6), so BG5:BG10. with results at the end of the macro pasted in CH5:CH10.

Then the second loops data range would start at BG11, and go down as many rows as the value in A11, with the results pasted in CH11 down.

It would be ideal if a maximum row value could also be specified too, to stop the loop. As I have 1,000s of rows of data, and it would be great to be able to run a subset, particularly while trialling.

Many thanks!

Sub Monte()

Set Data = Workbooks("21-02 Live Data.xlsb").Sheets("data")
Set Model = Workbooks("monte_carlo.xlsb").Sheets("model")

'clear current selection
Model.Range("B2", "B17").ClearContents
'get new data (column 59 = BG)
Data.Range(Cells(5, 59), Cells(11, 59)).Copy
'paste in new data
Model.Range("B2").PasteSpecial Paste:=xlPasteValues, Transpose:=True
'get results
Range("Q5", Range("Q5").End(xlDown)).Copy
'paste results
Data.Range("CH5").PasteSpecial xlPasteValues
End Sub

 

[Zot]

This value in column A on in which worksheet?
What are the contents in B2 to B17?

In each loop you always copy to Model.Range("B2") and clear it in each loop?
What is the Range("Q5")? from Sheet Data or Model? Always copy same Q5 to end of row?