Why cannot I not access a worksheet by name?

Question

I'm using Excel 2013. I can access a worksheet by iterating through the worksheets and checking the Name property, but I cannot find it individually by using the worksheet name as a key.

Do I really have to iterate through all the worksheets and then all the pivot tables to find the one I need to refresh?

This works:

Dim oWorksheet As Worksheet
Dim oPivot As PivotTable

For Each oWorksheet In ActiveWorkbook.Worksheets
    If oWorksheet.Name = "FPSpivot" Then
        For Each oPivot In oWorksheet.PivotTables
            oPivot.PivotCache.Refresh
        Next oPivot
    End If
Next oWorksheet

This doesn't work:

Dim oWorksheet As Worksheet
oWorksheet = ActiveWorkbook.Worksheets("FPSpivot")

I get the old chestnut:

Object variable or With block variable not set.

Why?

Ideally, I just want the single line:

ActiveWorkbook.Worksheets("FPSpivot").PivotTables("FPSpivot").PivotCache.Refresh

Surely this must be possible?

Answer 1

The error

Object variable or With block variable not set.

occurs because if you work with objects you need to use Set.

Dim oWorksheet As Worksheet
Set oWorksheet = ActiveWorkbook.Worksheets("FPSpivot")

Additionally:
Are you sure you mean ActiveWorkbook (the workbook which has focus / is on top) or did you mean ThisWorkbook (the workbook the code is written in)? Makes a huge difference and in most cases you need ThisWorkbook.