Copy a row using VBA

[Eric_G]

I would like to perform the following functions:

- copy the formulae in cells C100 to AA100
- paste the formulae above into cells Cxxx to AAxxx where "xxx" represents
the row number associated with the value in Cell C105 (in other words if cell
C105 reflects the value "75", the values would be pasted in cell C75 to AA75
- COPY and PASTE VALUES of cells C [75 - 1] or C&$ to AA74 (i.e. the value
of Cell 105 LESS 1 or in other words, the one row up from the row indicated
by cell 105)

 

[OssieMac]

Hi Eric,

Not sure if I have interpreted this correctly.

You want to copy the formulas from range C100:AA100 and paste the formulas
to a row determined by the value in C105.

You want to copy from the range C100:AA100 and paste values to the row above
the new location?

or did you want to copy from the formulas in the new location and paste
special values to a row above the new location.

The following code has alternative lines for the second copy ready to paste
the values. Comment out or delete the one you do not want. (One is already
commented out)

With Sheets("Sheet1")
.Range("C100:AA100").Copy _
Destination:=.Range("C" & .Range("C105"))

'Following code copies from the original location
'for the paste spoecial values
.Range("C100:AA100").Copy

'Following code copies from the new location of the formulas
'for the paste spoecial values
'.Range("C" & .Range("C105") & ":AA" & .Range("C105")).Copy

'Pastes the values to the row above the first paste.
.Range("C" & .Range("C105") - 1).PasteSpecial _
Paste:=xlPasteValues
End With

Application.CutCopyMode = False