Paste a range

[sweeneytime]

Hi guys,

I have a range ("B2:J" & Last Row) which takes an input, calculates, then I wish to paste the results to the last row of another sheet. I could do it with an ordinary selection.copy and paste, but the loop runs approx 4000 times and is rather slow.

I found some efficent code I am trying to adapt.

Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value

My attempt: But it only pastes in the first cell of the range. I need to paste the whole range. Some sort of ("A" & LR:"I" & LR)

Sheets("Pivot Data").Range("A" & LR2 + 1).Value = Sheets("Data Setup").Range("B2:J" & LR1).Value

Any solutions or other suggestions would be appreciated.

Cheers,
Sweeneytime

 

[HaHoBe]

Hi, sweeneytime,

you may try

Sheets("Pivot Data").Range("A" & LR2 + 1 & ":I" & LR2 + LR1).Value = Sheets("Data Setup").Range("B2:J" & LR1).Value

or

Sheets("Pivot Data").Range("A" & LR2 + 1).Resize(LR1 - 1, 9).Value = Sheets("Data Setup").Range("B2:J" & LR1).Value

If there aren´t any formulas in the area to copy you might like to check

Sheets("Data Setup").Range("B2:J" & LR1).Copy Destination:=Sheets("Pivot Data").Range("A" & LR2 + 1)

which will copy the format as well.

Ciao,
Holger

 

[sweeneytime]

Thanks Holger, the first one has worked nicely. Cheers