2

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?

GSerg
  • 76,472
  • 17
  • 159
  • 346
Mark Roworth
  • 409
  • 2
  • 15
  • 2
    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`. • Also note that you need to use `Set` for objects like putting a worksheet into a variable use `Set oWorksheet = ActiveWorkbook.Worksheets("FPSpivot")` – Pᴇʜ Sep 09 '19 at 09:34
  • Using `activeworkbook`, `activesheet` and `.select` [is generally considered bad practise.](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) As it invites errors like the one @Pᴇʜ pointed out. – Plutian Sep 09 '19 at 09:38
  • I'm still unclear as to why ```ActiveWorkbook.Worksheets("FPSpivot").PivotTables("FPSpivot").PivotCache.Refresh``` doesn't work. It complains with "Unable to get the PivotTables property of the Worksheet class". – Mark Roworth Sep 09 '19 at 10:11
  • @MarkRoworth That means your pivot table is not named "FPSpivot". – GSerg Sep 09 '19 at 11:05

1 Answers1

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 1
    Ah, thank you. Firstly, yes, I mean ThisWorkbook. And secondly, I use .Net so much I forgot you need a Set in VBA. Strange how sometimes you don't see the wood for the tree. Thank you for your help. Spot on. – Mark Roworth Sep 09 '19 at 09:52