asked on

Excel - Copy and paste without selecting

Hi,

The following piece of code works fine:
.Range(.Cells(firstRow,1) .Cells(lastRow,1)) = Sheets("Temp").Range("FB" & firstRow & ":FB" & lastRow).Value

When I try to make the columns dynamic (using R1C1 style) in the source sheet where aCol = 157

.Range(.Cells(firstRow,1) .Cells(lastRow,1)) = Sheets("Temp").Range(firstRow, aCol), cells(lastRow, aCol)).Value

I get an error "Application-defined or object defined error"?

Saqib Husain

Both statements give a syntax error

Try these instead

.Range(.Cells(firstRow, 1), .Cells(lastRow, 1)) = Sheets("Temp").Range("FB" & firstRow & ":FB" & lastRow).Value

.Range(.Cells(firstRow, 1), .Cells(lastRow, 1)) = Sheets("Temp").Range(Cells(firstRow, aCol), Cells(lastRow, aCol)).Value

adb11a

ASKER

Oops - I already had that - I accidently posted my question without the 'Cells'.

adb11a

ASKER

Oops - I already had that - I accidently posted my question without the 'Cells'.

Any other ideas?

Saqib Husain

just before this line check the values of acol, firstrow, lastrow and make sure they are good.

adb11a

ASKER

Yes, they are.

If I do the following it works...

Sheets("Temp").Select
Range(Cells(firstRow, aCol), Cells(lastRow, aCol)).Select

So the range is ok but the method doesn't work. It does if the columns are alpha (eg FB)

adb11a

ASKER

It also works if I do the following:
aCol = "FB"
.Range(.Cells(firstRow, 1), .Cells(lastRow, 1)) = Sheets("Temp").Range(aCol & firstRow & ":" & aCol & lastRow).Value

So my get around will be to convert the column number (eg 157) to 'FB' and use this method unless you can assist....

Thanks for your help to date

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adb11a

ASKER

Thanks Saquib.

Quick, clear and concise expert solution provided.