Why does assigning a reference in my spreadsheet sometimes work and sometimes not?

Question

I have a few cells in my excel workbook which are available for a client to put his own values. I wanted the workbook to initialize those cells with default values. In order to do so I have a worksheet "Arkusz do makr", where I store the values.

In a module "GM" I declare a variable to reference my worksheet easier like this:

Public M As Worksheet

Then I initialize this variable and set my default values like this (in ThisWorkbook):

Private Sub Workbook_Open()

    Set M = Worksheets("Arkusz do makr")

    Worksheets("Values").Range("Value1") = M.Range("Value1")
    Worksheets("Values").Range("Value2") = M.Range("Value2")
    Worksheets("Values").Range("Value3") = M.Range("Value3") `etc
End Sub

Now sometimes this works like a charm, and sometimes, when I open the workbook I get a

Run-time error '91': Object variable or With block variable not set.

Could someone please explain this behaviour to me? Additionally I would like to ask if my approach makes sense, since I have a hard time grasping the order of events in excel as well as the range of its objects.

EDIT: Additionally I should mention that the Debug function highlights the first Worksheets... line in my code. In specific worksheets I reference the M object as well, though I thought it changes anything here...

Answer 1

Try to change the code of this Sub like below.

I have added a simple error handling - if there is no worksheet "Arkusze do makr" or "Values" in your workbook, warning message is displayed and default values are not copied.

You can find more comments in code.

Private Sub Workbook_Open()
    Dim macrosSheet As Excel.Worksheet
    Dim valuesSheet As Excel.Worksheet
    '------------------------------------------------------------------


    With ThisWorkbook
        'This command is added to prevent VBA from throwing
        'error if worksheet is not found. In such case variable
        'will have Nothing as its value. Later on, we check
        'the values assigned to those variables and only if both
        'of them are different than Nothing the code will continue.
        On Error Resume Next
        Set macrosSheet = .Worksheets("Arkusz do makr")
        Set valuesSheet = .Worksheets("Values")
        On Error GoTo 0       'Restore default error behaviour.
    End With


    'Check if sheets [Values] and [Arkusz do makr] have been found.
    'If any of them has not been found, a proper error message is shown.
    'In such case default values are not set.
    If valuesSheet Is Nothing Then
        Call VBA.MsgBox("Sheet [Values] not found")
    ElseIf macrosSheet Is Nothing Then
        Call VBA.MsgBox("Sheet [Arkusz do makr] not found")
    Else

        'If both sheets are found, default values are copied
        'from [Arkusz do makr] to [Values].
        'Note that if there is no Range named "Value1" (or "Value2" etc.)
        'in any of this worksheet, another error will be thrown.
        'You can add error-handling for this case, similarly as above.
        With valuesSheet
            .Range("Value1") = macrosSheet.Range("Value1")
            .Range("Value2") = macrosSheet.Range("Value2")
            .Range("Value3") = macrosSheet.Range("Value3")
        End With
    End If

End Sub