Setting range syntax

[Zack Barresse]

Okay, end of a very long day. Having syntax issues - yet again. The following line is giving me problems ...

Set rng = wb.Sheet2.Range("A2:A" & wb.Sheet2.Range("A65536").End(xlUp).row)

wb is a workbook, set as Set wb = Workbooks("WkbkName.xls"). I get a error 438, object doesn't support this property or method. Does anybody know what I'm doing here, I'm sure it's simple..

[Daniel Klann]

G'day mate, just a little change needed:-

Set rng = wb.Sheets("Sheet2").Range("A2:A" & _
wb.Sheets("Sheet2").Range("A65536").End(xlUp).Row)

Dan

[Zack Barresse]

Hi Dan, thanks for that! So, to pick your brain here .. you're saying just change the way I'm referencing the sheet?

[Jacob Hilderbrand]

The code name of the sheet is not a collection item of the workbook. The sheet tab name is, but the code name is a class module in the VBE. If you type wb. you will see that there are no sheet code names listed. Seems odd to me, but that's the way it is.

You can set a worksheet variable and include the workbook:

Set ws = Workbooks("WkbkName.xls"). Sheets("Sheet2")
Set rng = ws.Range("A2:A" & ws.Range("A65536").End(xlUp).Row)

[Zack Barresse]

Thanks Jake, that's what I ended up going with. Interesting how that syntax plays out. I figured they (MS) would have allowed for both ways. Oh well. Thanks.