CODEWITHSUNDEEP
excelvbapivot-tableexcel-2013worksheet
Mark Roworth
Mark Roworth

Reputation: 566

Why cannot I not access a worksheet by name?

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?

Upvotes: 2

Views: 106

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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.

Upvotes: 1

Related Questions