What is wrong with this? - Xtreme Visual Basic Talk

06-20-2005, 02:12 AM

sirenko

Regular

Join Date: Dec 2004

Location: Ankara, Turkey

Posts: 64

What is wrong with this?

Hi, I could not see why it gets error.
It gives "object" error at the point written in bold.

Code:

 With Workbooks("A.XLS").Worksheets("A")
         .Range("2:2").Insert
         .Range(Cells(2, 9), Cells(2, 11)).Value = .Range(Cells(3, 9), Cells(3, 11)).Value
         End With

Thanks...

06-20-2005, 03:18 AM

italkid

Down...

Retired Moderator
* Expert *

Join Date: Dec 2002

Location: Belgium.

Posts: 6,731

You refer to "Cells" from which workbook/sheet...

So, in your code "Cells(2, 11)).Value" for instance will refer to the active sheet, no matter which one it is. If you don't want that to happen you'll have to put a dot in front of it:

Code:

With Workbooks("A.XLS").Worksheets("A")
  .Range("2:2").Insert
  .Range(.Cells(2, 9), .Cells(2, 11)).Value = .Range(.Cells(3, 9), .Cells(3, 11)).Value
End With

06-20-2005, 09:53 AM

NateO

Excellent…

Forum Leader
* Expert *

Join Date: Jun 2004

Location: Minneapolis MN - deceased

Posts: 2,483

Try the following:

Code:

Range(.Cells(2, 9), .Cells(2, 11)).Value = Range(.Cells(3, 9), .Cells(3, 11)).Value

Qualify the ranges passed in the Range reference, but not the outer Range, it knows what to do from there.

__________________
Regards,
Nate Oliver
Microsoft Excel MVP

06-20-2005, 04:44 PM

sirenko

Regular

Join Date: Dec 2004

Location: Ankara, Turkey

Posts: 64

Thank you for all worthy replies.