'With Worksheets("xxxx")' works only when "xxxx" is the active worksheet

Question

I'm pretty new to Excel VBA. So far I've read and learned a lot on this site, but haven't found a solution for my problem.

As part of a macro I have the following code:

With Worksheets("Oracle")
    On error resume next
    ActiveWorkbook.Names("bron").Delete
    ActiveWorkbook.Names.Add Name:="bron", RefersTo:= Range("A1", Range("A1").End(xlToRight).End(xlDown))
    .Cells.Select
    With Selection.Font
        .Name = "Verdana"
        .FontStyle = "Standaard"
        .Size = 8
    End With
    .Range("A1", Range("A1").End(xlToRight)).Font.Bold = True
    MsgBox "Tabblad ‘Oracle’ is klaar!", vbOKOnly
End With

I understand that with the first line of the code it shouldn't matter what the active sheet actually is. But the problem is it only works when Oracle is the active sheet. What have I done wrong?

Answer 1

If you are using With Worksheets() ... End With it means that you want to refer to a specific worksheet and not to the ActiveSheet. This is considered a good practice in VBA.

As mentioned in the comments by @GSerg, your code does not work, because you do not have a dot in front of all the Ranges. However, you cannot notice this because you are using On Error Resume Next, which ignores all errors.

In your case the problem is that you are trying to Refer to a range which is in both the ActiveSheet and in Oracle with this line .Range("A1", Range("A1").End(xlToRight)).. Thus the error is unevitable.

You have two options to make sure your code works:

1. Simply activate Worksheet "Oracle" and run the code. It will work ok.
2. Try to rewrite it like this:

---

With Worksheets("Oracle")
    On Error Resume Next
    ActiveWorkbook.Names("bron").Delete
    ActiveWorkbook.Names.Add Name:="bron", _
                          RefersTo:=.Range("A1", .Range("A1").End(xlToRight).End(xlDown))

        With .Cells.Font
            .Name = "Verdana"
            .FontStyle = "Standaard"
            .Size = 8
        End With

    .Range("A1", .Range("A1").End(xlToRight)).Font.Bold = True
    MsgBox "Tabblad ‘Oracle’ is klaar!", vbOKOnly

End With

Take a look that all the Ranges are referred with a dot and the Select command is not used any more.