Worksheet/Workbook properties

[Edmond]

Hi everyone,

I have few questions about workbook and worksheet properties.
I have working with several workbooks and worksheets.
Here is an extract of my code (which works):

Sub xlsfiles_update()



Set wbDD=Workbooks.Open(sFile1)
Set wbOO=Workbooks.Open(sFile2)
Set wb=Workbooks(sFile3)


Set wsDD=wbDD.Worksheets("Sheet1")
Set wsOO=wbOO.Worksheets("Sheet1")
Set ws1=wb.Worksheets("Shee1")
Set ws2=wb.Worksheets("Sheet2")
Set wsCAL=wb.Worksheets("Sheet3")






LastRowDD=wsDD.Cells(rows.count,2).End(xlUp).Row
LastRowOO=wsOO.Cells(rows.Count,2).End(xlUp).Row


wbDD.Activate
ValDD=wsDD.Range(Cells(2,1),Cells(LastRowDD,88)).Value


wbOO.Activate
ValOO=wsOO.Range(Cells(2,1),Cells(LastRowOO,23)).Value


ws1.Activate
ws1.Range(Cells(2,1),Cells(LastRowOO,23)).Value=ValOO
ws2.Activate
ws2.Range(Cells(2,1),Cells(LastRowDD,88)).Value=ValDD




wsCAL.Activate


End Sub

On the last lines, you can notice that I've activated the worksheet before transferring the values.
But I don't understand why it is necessary (if I remove ws1.Activate it doesn't work) to do it whereas I set up my variable as follows: wb.Worksheets("...")

To me, I am referencing twice the workbook but the Macro disagrees... Could you explain?

Thanks a lot in advance.

[Paul_Hossler]

Cells (as well as many other WS properties) used like that (no WS reference) refers to the ActiveSheet

ws1.Activate
ws1.Range(Cells(2,1),Cells(LastRowOO,23)).Value=ValOO

Something like this would probably work

ws1.Range(ws1.Cells(2,1),ws1.Cells(LastRowOO,23)).Value=ValOO

or


Range(ws1.Cells(2,1),ws1.Cells(LastRowOO,23)).Value=ValOO

[MagPower]

Hi Edmond,

Note that you have "Shee1" - rather than "Sheet1" - in the 'Set ws1= ...' statement at the top.

Russ.

[Aflatoon]

Cells (as well as many other WS properties) used like that (no WS reference) refers to the ActiveSheet

Unless the code is in a worksheet code module, in which case it refers to that sheet, whether it's active or not.

The ws1.Range(ws1.Cells(), ws1.Cells()) version is the one to use - always qualify both Range and Cells.

[Edmond]

Thank you so much all of you for your answers.
It perfectly works when I refer to a worksheet before the cell.

Have a great day,

[Paul_Hossler]

Unless the code is in a worksheet code module, in which case it refers to that sheet, whether it's active or not.

The ws1.Range(ws1.Cells(), ws1.Cells()) version is the one to use - always qualify both Range and Cells.

1. Yes, this example I assumed was in a standard module because of all the .Activate's , but you're right

2. Possible a best practice, but redundant I think if the components can make a valid range on the correct worksheet (fails if they don't)

Option Explicit
'1 - 10 in Sheet2.Range("A1:A10")
Sub test()
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    ws1.Select
    
    'wrong answer
    MsgBox Application.WorksheetFunction.Sum(Range(Cells(1, 1), Cells(1, 1).End(xlDown)))
    
    'right answer
    MsgBox Application.WorksheetFunction.Sum(ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)))
    MsgBox ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)).Address(1, 1, 1, 1)


    'also right answer
    MsgBox Application.WorksheetFunction.Sum(Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)))
    MsgBox Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)).Address(1, 1, 1, 1)


    'fails
'    MsgBox Application.WorksheetFunction.Sum(ws1.Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)))
'    MsgBox ws1.Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)).Address(1, 1, 1, 1)


    'fails
'    MsgBox Application.WorksheetFunction.Sum(Range(ws2.Cells(1, 1), ws1.Cells(1, 1).End(xlDown)))
'    MsgBox Range(ws2.Cells(1, 1), ws1.Cells(1, 1).End(xlDown)).Address(1, 1, 1, 1)

End Sub

Just my nit picky opinion

[Aflatoon]

Put this:

MsgBox Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)).Address(1, 1, 1, 1)

into the code module of any worksheet other than ws2 and then try it.

[Edmond]

Thank you again!
Indeed the combination MsgBox Address() is very useful!!
Have a nice day everyone