Quick Question - Referencing cell value from another workbook

[Jameo]

Just a quickie guys,

If I want to reference a cell in another workbook (and it is currently open) I could use

Workbooks("Book2).Sheets("Sheet1").Cells(1,1).value


Similarily I could use

dim sht as Worksheet

set sht = Worksheets("Sheet1")

Workbooks("Book2").sht.cells(1,1).Value


However, why can I not use:

Dim wb as Workbook
Dim sht as Worksheet

Set wb = Workbooks("Book1")
Set sht = Worksheets("Sheet1")

wb.sht.cells(1,1).value

Why the hell does that not work? Lol

 

[VoG]

Perhaps

Set sht = wb.Worksheets("Sheet1")
x=sht.Cells(1,1).Value

 

[RoryA]

Your second example will not work, as it happens, for the same reason the third one doesn't - the sht variable is not a property of the wb workbook. Your third one ought to be:

Dim wb as Workbook
Dim sht as Worksheet

Set wb = Workbooks("Book1")
Set sht = wb.Worksheets("Sheet1")

sht.cells(1,1).value = "something"

 

[Jameo]

Ah I see, nice one guys.

Cheers