Range(Cell1,Cell2) Error on another workbook controlling some other workbook?

tinfanide · #1 02-09-2012, 03:39 AM

Code:

Sub test()

Dim book As Workbook
Dim sheet As Worksheet

Set book = Workbooks("test.xls")
Set sheet = book.Worksheets("Sheet1")

sheet.Activate

''' Application defined or Object defined error
sheet.Range(Cells(23, 2), Cells(47, 2)).Select
''' the below one didn't return an error
''' sheet.Range("B23:B47").Select

Set book = Nothing
Set sheet = Nothing
End Sub

When I run the Range(Cell1, Cell2).Select on a local worksheet, no problem.

But when I run the command on a local worksheet to select a range of cells on another worksheet, it returns the error (see the comment above)

macropod · #2 02-09-2012, 04:08 PM

Hi tinfanide,

It's because your 'Cells' references don't point to the 'sheet'.

BTW, it's not good practice to use names like 'sheet', as you can very quickly get that messed up with '.Sheet'. Furthermore, I see you're still trying to select things. You rarely need to select anything. For example:

Code:

Sub Test()
Dim xlWkBk As Workbook
Dim xlSht As Worksheet
Set xlWkBk = Workbooks("test.xls")
Set xlSht = xlWkBk.Worksheets("Sheet1")
With xlSht
  'do something, eg define a range to work on:
  With .Range(.Cells(23, 2), .Cells(47, 2))
    'do something, eg populate the defined range:
    .Value = Now
  End With
End With
Set xlSht = Nothing: Set xlWkBk = Nothing
End Sub

PS: Your question concerns Excel vba, but you posted it in the Word vba forum. I've moved it to the Excel forum.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Shared Workbook	SJT	Excel	6	01-12-2012 11:33 AM
Loop through folder of workbooks and copy range to other workbook	Snvlsfoal	Excel Programming	3	07-29-2011 05:55 AM
macro to transfer data from one workbook to another workbook	virsojour	Excel Programming	5	02-01-2011 08:58 PM
Select a range in one one workbook while working in other workbook	Slow&Steady	Excel	1	02-21-2010 03:34 AM
Share Workbook cannot merge	Ichigo	Excel	3	01-19-2010 03:05 AM