Macro for calculate, copy, paste loop

Hi all,

I have a spreadsheet with input data from a large number of cases, referred to as ID numbers. For each ID, I have multiple data points on several variables. For each ID, I then compute scores on each of the variables, by taking sums, averages, etc. across all the values for each ID.

In my spreadsheet, I calculate scores for one ID number at a time. This is done by placing an ID number in a “reference” cell. All the formulas that calculate variable scores then refer to the reference cell to determine which ID number to calculate the variable scores for. When I have calculated variable values for one ID, I can copy these values, and paste (paste values) to an “output” spreadsheet tab. I then move on to entering a new ID in the reference cell.

What I need to do is to be able to automate the process of entering an ID number in the reference cell, and copying/pasting the results from the calculation formulas into the “output” tab. The functionality I am aiming for is to have to two cells where I specify the range of ID numbers to loop through. The routine would then run through the calculation/copy/paste steps for all ID numbers between the first specified ID number and the second specified ID number.

I have attached a spreadsheet file that illustrates the process. Note that the attached spreadsheet is a simplified version of the much more complex spreadsheet I am using. The structure is the same, however.

I am very hopeful that this would be quite easy to achieve with a simple VB loop function, combined with a copy-paste/special function. Unfortunately, I am very new to VB and macros, so this challenge is beyond my current skill level. I would therefore be extremely grateful for any help, tips, or assistance with this!

Many thanks!

S

Perhaps like this?

Sub x()

Dim i As Long

With Sheets("Calculation & controls")
    For i = .Range("J6") To .Range("J7")
        .Range("J1") = i
        Sheets("Output").Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 4) = .Range("A7").Resize(, 4).Value
    Next i
End With

End Sub

Yes, this does exactly what I was looking for. Thanks very much!!!