View Full Version : Compile error
rboothe
11-03-2010, 03:54 PM
Hi,
I'm still a newbie!
Trying to copy a range of cells in a row (starting at 6 columns from the cursor thru to col. 11 - in same row) and pasting it in cell G45.
I keep getting compile error...":" is highlighted. See code below :
Range(ActiveCell.Offset(0,6):ActiveCell.Offset(0,11)).Copy Range("G45")
What ami doing wrong ...and what if i need to PasteSpecial instead of regualar paste (in need to transpose).
lynnnow
11-03-2010, 04:01 PM
Try this:
Range(Cells(ActiveCell.Row, 6), Cells(ActiveCell.Row, 11).Copy Range("G45")
Bob Phillips
11-03-2010, 04:02 PM
With ActiveCell
Range(.Offset(0,6), .Offset(0,11)).Copy Range("G45")
End With
rboothe
11-03-2010, 05:16 PM
Guys,
Just got back..
This one gave compile error:
Range(Cells(ActiveCell.Row, 6), Cells(ActiveCell.Row, 11).Copy Range("G45")
But this one worked :
With ActiveCell
Range(.Offset(0, 6), .Offset(0, 11)).Copy Range("G45")
End With
Thanks a lot....Now how would i Do a PasteSpecial with transpose ?
lynnnow
11-03-2010, 05:19 PM
With ActiveCell
Range(.Offset(0, 6), .Offset(0, 11)).Copy
Range("G45").PasteSpecial Transpose:=True
End With
rboothe
11-03-2010, 05:26 PM
Wow !!
It worked!
Now i'll try what i really need to do...copy from my orders sheet and paste to my sheet where quotes are prepared ...
Thanks again!
rboothe
11-03-2010, 08:44 PM
Back again..
I tried pasting info to a different file and got Runtime error 9...Subscript Out of Range. Here's the code:
With ActiveCell
Range(.Offset(0, 6), .Offset(0, 12)).Copy
Workbooks("pnvtest.xls").Sheets("Details").Range("addressLine1").PasteSpecial xlPasteFormulas, Transpose:=True
End With
Help!
Bob Phillips
11-04-2010, 12:57 AM
Worked fine in my test. Is that workbook open?
rboothe
11-04-2010, 07:18 AM
XLD,
It works when the workbook is open...it wasn't !
I tried including the file path to see if it would work with the target file closed. See code :
With ActiveCell
Range(.Offset(0, 6), .Offset(0, 10)).Copy
Workbooks("C:\New Drive C\Education\VBA\R & D\pnvtest.xls").Sheets("Details").Range("addressLine1").PasteSpecial xlPasteFormulas, Transpose:=True
End With
It came back with the same "Subscript out of range" error. Does that mean i'll have to include a FileOpen command and FileClose each time ??
Bob Phillips
11-04-2010, 07:38 AM
You can't copy to a closed workbook, it has to be open. I would add a test to see if it were open, and open it if not.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.