Solved

Excel - Copy and paste without selecting

Posted on 2015-01-10
8
137 Views
Last Modified: 2015-01-10
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"?
0
Comment
Question by:adb11a
  • 5
  • 3
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40542461
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
 

Author Comment

by:adb11a
ID: 40542482
Oops - I already had that - I accidently posted my question without the 'Cells'.
0
 

Author Comment

by:adb11a
ID: 40542486
Oops - I already had that - I accidently posted my question without the 'Cells'.  

Any other ideas?
0
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40542487
just before this line check the values of acol, firstrow, lastrow and make sure they are good.
0
 

Author Comment

by:adb11a
ID: 40542491
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
 

Author Comment

by:adb11a
ID: 40542496
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 40542500
Try

.Range(.Cells(firstrow, 1), .Cells(lastrow, 1)).Value = Sheets("Temp").Range(Sheets("Temp").Cells(firstrow, acol), Sheets("Temp").Cells(lastrow, acol)).Value
0
 

Author Closing Comment

by:adb11a
ID: 40542518
Thanks Saquib.

Quick, clear and concise expert solution provided.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question