Proper Workbook and Worksheet object references

[rjwebgraphix]

This is just for learning sake, so no need to rush an answer.

I was working on some code to help someone else out and ran into a snag when working between two workbooks.

This is a shortened version to ensure I covered relevant variables and declarations....

Sub whynotwork()
                    
'This workbook
Dim wbCodeBook As Workbook
Set wbCodeBook = ThisWorkbook

'Set worksheet
Dim wsCodeBook As Worksheet
Set wsCodeBook = Sheets("Sheet1")

'set last row of worksheet
Dim lrCodeBook As Long
lrCodeBook = Workbooks(wbCodeBook.Name).Sheets(wsCodeBook.Name).Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
'lrCodeBook = wbCodeBook.wsCodeBook.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

'open file
Dim wbDataBook As Workbook
Set wbDataBook = Workbooks.Open(Filename:="C:\temp\file1.xls", UpdateLinks:=0)

'last row and column of file
Dim lrDataBook As Long
Dim lcDatabook As Long
lrDataBook = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
lcDatabook = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column

'set range to copy
Dim c1 As Range, c2 As Range, rng As Range
Set c1 = wbDataBook.ActiveSheet.Cells(2, "A")
Set c2 = wbDataBook.ActiveSheet.Cells(lrDataBook, lcDatabook)
Set rng = wbDataBook.ActiveSheet.Range(c1, c2)

rng.Copy Destination:=Workbooks(wbCodeBook.Name).Sheets(wsCodeBook.Name).Range("A" & lrCodeBook + 1)
'rng.Copy Destination:=wbCodeBook.wsCodeBook.Range("A" & lrCodeBook + 1)

End Sub

I'd like to focus on 2 lines of this.

'This one
lrCodeBook = Workbooks(wbCodeBook.Name).Sheets(wsCodeBook.Name).Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

'and this one
rng.Copy Destination:=Workbooks(wbCodeBook.Name).Sheets(wsCodeBook.Name).Range("A" & lrCodeBook + 1)

I thought that since the workbook and worksheet were already set as objects, I should just be able to write it like this...

'first line
lrCodeBook = wbCodeBook.wsCodeBook.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

'second line
rng.Copy Destination:=wbCodeBook.wsCodeBook.Range("A" & lrCodeBook + 1)

but that didn't work, it produces an error and drove me batty for a bit.

I ended up just settling for writing them with....

Workbooks(wbCodeBook.Name) and Sheets(wsCodeBook.Name)

... and that works, but I thought the purpose of setting the object in the first place was to be able to reference it by its variable name.

Any ideas why that didn't work and what would be the proper way to reference these objects?

Thanks.

 

[Kyle123]

The reason that it doesn't work is that a worksheet object is not a property of the ThisWorkbook class. You'll notice that through ThisWorkbook, you can't reference sheets by their code name - ThisWorkbook.Sheet1 isn't valid since there is no property in the ThisWorkbook class that is a worksheet. Instead, they are contained in a collection called sheets, so you need to reference it by it's string name - you've actually already done this, but it needs to be more explicit:

Set wsCodeBook = Sheets("Sheet1")

Is actually shorthand for:

Set wsCodeBook = ActiveWorkbook.Sheets("Sheet1")

The above should technically be:

Set wsCodeBook = ThisWorkbook.Sheets("Sheet1")

or in your example:

Sub whywontwork()
'This workbook
Dim wbCodeBook As Workbook
Set wbCodeBook = ThisWorkbook


'Set worksheet
Dim wsCodeBook As Worksheet
Set wsCodeBook = wbCodeBook.Sheets("Sheet1")


'set last row of worksheet
'Dim lrCodeBook As Long
'lrCodeBook = Workbooks(wbCodeBook.Name).Sheets(wsCodeBook.Name).Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
Debug.Print wsCodeBook.Cells(1, 1).Value


End Sub

Does that clear things up any?

 

[rjwebgraphix]

Perfectly clear and tested.

I thought I would have to reference the workbook and the sheet separately, but this way, you reference the workbook once when setting the sheet variable and then any uses after that, you just need reference the sheet by its variable, because it already knows which workbook. and Sheets("Sheet1") by itself references the activeworkbook, not the other workbook.

Got it. So minor, yet so made a few more hairs fall out.

 

[Kyle123]

Glad its sorted