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"?
adb11aAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try

.Range(.Cells(firstrow, 1), .Cells(lastrow, 1)).Value = Sheets("Temp").Range(Sheets("Temp").Cells(firstrow, acol), Sheets("Temp").Cells(lastrow, acol)).Value
0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
adb11aAuthor Commented:
Oops - I already had that - I accidently posted my question without the 'Cells'.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

Any other ideas?
0
 
Saqib Husain, SyedEngineerCommented:
just before this line check the values of acol, firstrow, lastrow and make sure they are good.
0
 
adb11aAuthor Commented:
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)
0
 
adb11aAuthor Commented:
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
0
 
adb11aAuthor Commented:
Thanks Saquib.

Quick, clear and concise expert solution provided.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.