Compile error

[rboothe]

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]

Try this:

Range(Cells(ActiveCell.Row, 6), Cells(ActiveCell.Row, 11).Copy Range("G45")

[Bob Phillips]

With ActiveCell

Range(.Offset(0,6), .Offset(0,11)).Copy Range("G45")
End With

[rboothe]

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]

With ActiveCell

Range(.Offset(0, 6), .Offset(0, 11)).Copy

Range("G45").PasteSpecial Transpose:=True
End With

[rboothe]

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]

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]

Worked fine in my test. Is that workbook open?

[rboothe]

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]

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.