Pivot error

[ChrisAch]

Hi all.

I am trying to include several pivot tables on 6 or so sheets on a work book. all of my other pivots are fine, but this particular one keebs debugging.

Any ideas why?

In particular setPTCache part of the below.


Sub TP2ISSUESEMIR()

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("chris")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)


Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), _
TableName:="PivotTable4")
PT.ManualUpdate = False


Sheets("chris").Select

End Sub


Thank you.

[Paul_Hossler]

Can you post a small workbook with one sheet of the source data for the PT macro that fails?

It will make it much easier to debug

[CubsFan]

This may or may not be your issue, but... Your code worked fine for me... until I put a columnar gap in my data. So, for example, with the following data, your code works fine.


A
B
C
D


1
Name
ID
Category
Value


2
Chris
1
A
2


3
Chris
1
B
1


4
Steve
2
A
5


5
Suzy
3
C
6


6
Suzy
3
B
2




However, the following sheet data throws an error. This error was because I have counted the col F as the final column but there is no data/header for column E




A
B
C
D
E
F


1
Name
ID
Category
Value

Qty


2
Chris
1
A
2

5


3
Chris
1
B
1

9


4
Steve
2
A
5

2


5
Suzy
3
C
6

4


6
Suzy
3
B
2

8

[ChrisAch]

Your right, the way I recieve the data, there is two columns where are not complete columns of data. However there is data throughout the length of the column, so I do require these columns to be accounted for in the pivot.


I realised, I performed the same code on a seperate piece of work which worked fine, as it ended on column I, however on the new set of data, it ends on J - which I believe to be the problem.

Any ideas on how to adapt the code to work?

Thank you kindly.

[p45cal]

A file, as Paul-Hossler says, would be really useful (no wrong guesses on our part).
Pivot table source data columns can be completely blank EXCEPT for the header.
What you can do is convert the range (WSD.Cells(1, 1).Resize(FinalRow, FinalCol)) to a proper table first and Excel will insert its own headers at the top of blank columns.
Then add the pivot, then optionally restore the table as it was.
Something along the lines of:
Sub TP2ISSUESEMIR()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Dim mytable 'will be a listobject.
Dim OriginalHeaders 'temp store.
Set WSD = Worksheets("chris")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
OriginalHeaders = PRange.Rows(1).Value 'store original table headers for restoration later.
Set mytable = WSD.ListObjects.Add(xlSrcRange, PRange, , xlYes) 'add a Table when new column headers will be added if necessary.
mytable.TableStyle = "" 'optional line to remove default style applied to tables for later reversion of table to a normal range.
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=mytable)
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2))
PT.ManualUpdate = False
mytable.Unlist 'optional to convert table back to normal range.
PRange.Rows(1).Value = OriginalHeaders 'optional; restore original headers, but only if Unlist line above executed.
End Sub

[ChrisAch]

Hi All.

I have attached some data in a simlar format to the raw data I am looking at.

Thank you kindly.


A file, as Paul-Hossler says, would be really useful (no wrong guesses on our part).
Pivot table source data columns can be completely blank EXCEPT for the header.
What you can do is convert the range (WSD.Cells(1, 1).Resize(FinalRow, FinalCol)) to a proper table first and Excel will insert its own headers at the top of blank columns.
Then add the pivot, then optionally restore the table as it was.
Something along the lines of:
Sub TP2ISSUESEMIR()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Dim mytable 'will be a listobject.
Dim OriginalHeaders 'temp store.
Set WSD = Worksheets("chris")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
OriginalHeaders = PRange.Rows(1).Value 'store original table headers for restoration later.
Set mytable = WSD.ListObjects.Add(xlSrcRange, PRange, , xlYes) 'add a Table when new column headers will be added if necessary.
mytable.TableStyle = "" 'optional line to remove default style applied to tables for later reversion of table to a normal range.
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=mytable)
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2))
PT.ManualUpdate = False
mytable.Unlist 'optional to convert table back to normal range.
PRange.Rows(1).Value = OriginalHeaders 'optional; restore original headers, but only if Unlist line above executed.
End Sub

[ChrisAch]

The code above works perfectly... Thank you so much....

im not sure why though, given all of my headers were present!?!

Thank you once again.

[p45cal]

Apart from not having sheet called chris in the workbook the code you supplied in msg#1 works fine on it.

[ChrisAch]

Would anyone know why the below code debugs when trying to update the pivot?


With ActiveSheet.PivotTables("PivotTable13").PivotFields("Country")
.Orientation = xlColumnField
.Position = 1
End With

I thought I had cracked this issue :-(

[p45cal]

probably because this time round the pivot table isn't called PivotTable13?
A complete guess because the code is so out of context.

(I expect you're glad to be staying in the UK but an increased chance of being moved to France?!)

[Paul_Hossler]

If there is only one PT on the sheet, then




With ActiveSheet.PivotTables(1).PivotFields("Country")
.Orientation = xlColumnField
.Position = 1
End With



would work regardless of the PT name

[ChrisAch]

That has fixed the issue... (1)

Thank you for your help....

[p45cal]

Cross posted here:http://www.mrexcel.com/forum/excel-questions/921822-pivot-cache-error.html
FYI Chris, you need to be aware of cross posting rules here and at MrExcel (you need to provide links).
Why? Read http://www.excelguru.ca/content.php?184

…and it would only be courteous to tell those at MrExcel that your problem is resolved (perhaps even with a link to here?)

[ChrisAch]

Apologies.

Of course that makes sense. I was unaware that the two sites were some way linked or that I could not post on seperate sites. Apologies.

I will link back to here on Mr Excel as we speak.

[ChrisAch]

On the back of the above.... not sure if a new thread is required, or continue here ...

As I have created a cache for each pivot to feature on the same page as the data etc, I am no longer able to add a slicer to the pivot table. Is there a way around this?

Im off to Mr Ecxel, to link back to here now :-)

[p45cal]

was unaware that the two sites were some way linkedThey're not, apart from dealing with similar subjects and the sets of individuals helping happen to overlap a good bit too; but that's beside the point. The second link in msg#13 explains all.




Ior that I could not post on seperate sitesYou can post on separate sites ('though check each site's own rules), but you're asked to provide links.

[p45cal]

not sure if a new thread is required, or continue here ...

As I have created a cache for each pivot to feature on the same page as the data etc, I am no longer able to add a slicer to the pivot table. Is there a way around this?

Im off to Mr Ecxel, to link back to here now :-)

If the cache is always the same data range then only create 1 cache and attach multiple pivots to it:
One line like this:
Set PTCache = ActiveWorkbook.PivotCaches.Add…

and multiple lines of this ilk:
Set PT = PTCache.CreatePivotTable…

Your code currently only shows one pivot table being produced, soi it would do to include your current code.