sheets.count vs workbook activation

Question

In the following code, the "Sheets.Count" in "Master.Activate" works only when the "Source" workbook is manually selected by mouse click before running the macro. Otherwise "Master.Activate" will activate the first worksheet of "Master" workbook instead of performing Sheets.Count and selecting the last sheet. That basically means that the macro has to be run from "Master" workbook but only after clicking on "Source" workbook. I would be grateful for any suggestions on how to fix that.

Dim Source As Worksheet
Set Source = Workbooks("Source.xlsx").Worksheets("Settlements")
Dim Master As Worksheet
Set Master = Workbooks("Master Bonviva.xlsm").Worksheets(Sheets.Count) 

Source.Activate

For Each cell In Source.Columns("M").Cells
    If Not IsEmpty(cell) Then
        Master.Activate
            For Each cell2 In Master.Columns("J").Cells
                If Not IsEmpty(cell2) Then
                    If cell = cell2 Then
                        cell2.Offset(0, 9).Value = cell.Offset(0, -2).Value
                        cell2.Offset(0, 8).Value = cell.Offset(0, -8).Value
                    End If
                End If
            Next cell2
     End If
Next cell

End Sub

Regards, Bartek

Apart from the use of `IsEmpty` instead of `= ""`, this is identical code to your last question, in which you stated that the code **worked** if the `Source.Activate` and `Master.Activate` were included, but not if they weren't. And it was pointed out that we could see no reason for that to occur - that the only issue with your code would be the unqualified `Sheets.Count` (which would have been an issue whether you included the `Activate` statements or not). — YowE3K, Nov 13 '17 at 18:54

score 2 · Accepted Answer · answered Nov 13 '17 at 16:24

The error is in

 Set Master = Workbooks("Master Bonviva.xlsm").Worksheets(Sheets.Count)

Without qualification Sheets.count refers to the number of sheets in whichever workbook is open at the moment it is run. Replace with this

Dim wb as workbook
Set wb =Workbooks("Master Bonviva.xlsm")
Set Master = wb.worksheets(wb.sheets.count)

score 1 · Answer 2 · answered Nov 13 '17 at 16:19

Try to disreagart Select and Activate. Declare the cell1 and cell2. Thus, this should work:

Public Sub TestMe()

    Dim Source As Worksheet
    Set Source = Workbooks("Source.xlsx").Worksheets("Settlements")

    Dim Master As Worksheet
    Set Master = Workbooks("Master Bonviva.xlsm").Worksheets(Sheets.Count)

    Dim cell1   As Range
    Dim cell2   As Range

    For Each cell1 In Source.Columns("M").Cells
        If Not IsEmpty(cell1) Then
            For Each cell2 In Master.Columns("J").Cells
                If Not IsEmpty(cell2) Then
                    If cell1 = cell2 Then
                        cell2.Offset(0, 9).Value = cell1.Offset(0, -2).Value
                        cell2.Offset(0, 8).Value = cell1.Offset(0, -8).Value
                    End If
                End If
            Next cell2
        End If
    Next cell1

End Sub

How to avoid using Select in Excel VBA

In general, as a good practice, do not declare variables, which use the same name used by the VB Editor. E.g., avoid declaring cell, but use cell1 or anything else which makes sense.

sheets.count vs workbook activation

2 Answers2