Last cell, copy, paste special, loop
 

Need help with the following:

I have data (equations) in columns P and Q. I need to copy and paste
special values the values in P2 and Q2 to R2 and S2.

I know simple...
But there are a few thousand rows of data and the data in columns P and Q
are based on equations that change based on how far down the R column is
filled down.

Currently I have something like this..
Range("r1", Range("r1").End(xlDown)).Offset(2, -2).Resize(1, 2).Copy
Range("r1", Range("r1").End(xlDown)).Offset(2, 0).Resize(1, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

But it doesn't go to the next set of cells after the initial copy and paste.

I also need to Loop this process for all rows.

Thanks in advance

Last cell, copy, paste special, loop
 

This goes from P2 down about 1000 rows. Adapt it to suit your needs:

Sub ak()
For i = 2 To 1000
Range("P" & i & ":Q" & i).Copy
Range("R" & i).PasteSpecial Paste:=xlPasteValues
Next
End Sub
--
Gary's Student
gsnu200704


"AK" wrote:

Need help with the following:

I have data (equations) in columns P and Q. I need to copy and paste
special values the values in P2 and Q2 to R2 and S2.

I know simple...
But there are a few thousand rows of data and the data in columns P and Q
are based on equations that change based on how far down the R column is
filled down.

Currently I have something like this..
Range("r1", Range("r1").End(xlDown)).Offset(2, -2).Resize(1, 2).Copy
Range("r1", Range("r1").End(xlDown)).Offset(2, 0).Resize(1, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

But it doesn't go to the next set of cells after the initial copy and paste.

I also need to Loop this process for all rows.

Thanks in advance

Last cell, copy, paste special, loop
 

Sub CopyDown()
Dim LRow As Long, i As Long

Application.ScreenUpdating = False
LRow = Cells(Rows.Count, 16).End(xlUp).Row

For i = 2 To LRow
Range("P" & i & ":Q" & i).Copy
Range("R" & i).PasteSpecial Paste:=xlPasteValues
Next

Application.ScreenUpdating = True
End Sub

You could set calculation to Manual to speed things up, however your
description indicates that formulas need to calculate as column R fills up.

Mike F
"AK" wrote in message
...
Need help with the following:

I have data (equations) in columns P and Q. I need to copy and paste
special values the values in P2 and Q2 to R2 and S2.

I know simple...
But there are a few thousand rows of data and the data in columns P and Q
are based on equations that change based on how far down the R column is
filled down.

Currently I have something like this..
Range("r1", Range("r1").End(xlDown)).Offset(2, -2).Resize(1, 2).Copy
Range("r1", Range("r1").End(xlDown)).Offset(2, 0).Resize(1, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

But it doesn't go to the next set of cells after the initial copy and
paste.

I also need to Loop this process for all rows.

Thanks in advance