Copy down values with VBA

[Alexandra]

Hi,

I'm having a workbook in which I want to summarise values with calculations from other sheets for each reference. Please see attached example workbook; In cell A2 of sheet "Summary" you can change the number of the reference from 1 to 15, the cells next to it return the calculated values from a different sheet. In the rows below I would like to have copy - pasted as values the calculated values for each of the references, eg in cell B3:F3 for reference 1, in cell B4:F4 for reference 2 etc etc.

Hopefully you have a simple solution for this.

Thank you

11798

[Alexandra]

Hi,

Apparently this issue is tougher to solve than I initially expected :(

Still, it shouldn't be that difficult if i read other solutions here in the forum. After playing and trying I came up with a solution using a loop, but it's not that elegant since it copies down formulas first and then pastes them as values. Also, I've read somewhere that loops are usually slowing things down which is why I would like to do the same thing in an alternative way. I reckon this loop solution will require a lot of memory once implemented in a bigger project, which is what i aim to do.

Here's the code that I have devised:


Sheets("Summary").Select


Range(Range("B2"), Range("B2").End(xlToRight)).AutoFill Destination:=Range("B2:f17")


For i = 1 To 15
Sheets("Summary").Range("A2").Value = Sheets("Summary").Cells(i + 2, 1).Value


Sheets("Summary").Range(Cells(i + 2, 1), Cells(i + 2, 70)).Value = Sheets("Summary").Range(Cells(i + 2, 1), Cells(i + 2, 70)).Value
Next i


Range("a1").Select


End Sub




It's also in the workbook attached.

11802

Is there an easier solution for achieving this?

Thanks!!