Highline Excel Class 20: Pivot Tables 20 Examples

I have set up a pivot table in a worksheet using a macro. I have also created a 2nd macro to refresh the pivot table.

I have place the code for the refresh marco, as well as code to copy the table, and to paste to a spreadsheet, behind a userform which contains the spreadsheet. Whilst the code appears to work i keep getting periodic failures with the error message:-

`unable to get pivot tables from the worksheet class!`

When i reset the tables the code works fine again, but the fails after a couple of runs. But i cant work out why this happens.

My code is:-

Private Sub userform_initialize()

activesheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
activesheet.PivotTables("PivotTable1").PivotCache.refresh
SimpleSheet.Sheet17.Select
Range("J1:Q22").Copy
purchasereport.Spreadsheet1.Range("A1").Paste
With Application
.WindowState = xlMaximized
Me.Top = .Top
Me.Left = .Top
Me.Height = .Height
Me.Width = .Width
End With
End Sub

I am hoping that someone can help me!!! I am new to pivot tables, and have been searching online for the last two days looking for examples. I am trying to see how I will be able to use them before I work too hard trying to build one that doesn't work for my boss's needs =)

THANK YOU!!

Hi friends!,

I have a data file, and another file with some pivot tables on different worksheets, each reading from the data file.

I am trying to write some code in the data file that tells the relevant pivot table to refresh anytime data changes in the source table. What I wrote doesn't work. I have pasted a sample of what i came up with, below.



Private Sub Worksheet_Change(ByVal Target As Range)
[file_name.xlsm] worksheet_name!.PivotTables("PivotTable1").RefreshTable
End Sub



Could anyone help me correct this code, or give me a better way to do this?

As always, I am very grateful for any ideas and suggestions.

How does the term 'pivot' apply to Excel's Pivot tables and Pivot Charts

Here I want to know whether i can get the Distinct or Unique Count of the different User ids
within the same Journal Entry Number column.



Example The pivot table has MTX-10001158 Journal Entry Number has

USER95
USER94
USER95
USER94

in the line and grlines level.


so i want count=2 for User id USER94
and count = 2 for User id USER95 within the same Journal Entry using Pivot table and so on for all the Journal Entry Numbers.


That is Distinct count of different user ids within the same Journal Entry Number using pivot table and
if possible the sum of count of user ids distict count.(example count =2 for user94 and count for user95 is 2 so sum is 2+2=4)

Please help me with this query.

Thank you.

Hi

Within my macro is some code which sorts 4 pivot tables on a worksheet. Elsewhere in the macro one can select the filters required on the pivot table which refreshes the pivots. However if a filter is selected that contains no data the Macro falls over on whichever pivot does not contain the data.

I guess I need an IF statement around the autosort lines of code supplied below which will move on to the next line if there is no data in the pivot.


ActiveSheet.PivotTables("PivotTable4").PivotFields("CLIENT_NAME").AutoSort _
xlAscending, "Sum of RANK_AREA", ActiveSheet.PivotTables("PivotTable4"). _
PivotColumnAxis.PivotLines(1), 1

ActiveSheet.PivotTables("PivotTable5").PivotFields("CLIENT_NAME").AutoSort _
xlAscending, "Sum of RANK_SECTOR", ActiveSheet.PivotTables("PivotTable5"). _
PivotColumnAxis.PivotLines(1), 1

ActiveSheet.PivotTables("PivotTable8").PivotFields("CLIENT_NAME").AutoSort _
xlAscending, "Sum of RANK_LBC", ActiveSheet.PivotTables("PivotTable8"). _
PivotColumnAxis.PivotLines(1), 1

ActiveSheet.PivotTables("PivotTable7").PivotFields("CLIENT_NAME").AutoSort _
xlAscending, "Sum of RANK_SECTOR_LBC", ActiveSheet.PivotTables("PivotTable7"). _
PivotColumnAxis.PivotLines(1), 1


Please help
Thanks
Charlie

i have a task of producing a spreadsheet that asks questions and takes figures then displays them as a report.

e.g how much is your energy bill? the user types a figure, its calculated in a formula, and the results displayed in a report.

I need it set out so it looks professional and user friendly. Has anyone got any examples of well laid out user friendly colourful spreadsheets?

Would appriciate it if some one could help me.

Thanks a great deal

Hello,

I am getting the above error on the code below. Basically I am trying to test each of the pivot items to see whether they are after the start date and before the end date and hide/;show them as appropriatte.

The if statement is working fine but the macro is falling over on the bolded lines. Note that I have also tried "PivotItem.visible=false" and PivotItems(PivotItem).visible = false" as well as the fuller version in the code, but all 3 get the same message.

Quote:

dteStartdate = Date - (Weekday(Date, vbSunday)) - 5
dteEnddate = startdate + 5


ActiveSheet.PivotTables("PivotTable1").PivotFields("Order Date").CurrentPage = _
"(All)"

For Each PivotItem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Order Date").PivotItems
If CDate(PivotItem) < dteStartdate Or CDate(PivotItem) > dteEnddate Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("Order Date").PivotItems(PivotItem).Visible = False
Else
PivotItem.Visible = True
End If

Next PivotItem
' End With

Hi All,

I am trying to use VBA to filter a pivot table in a report that I produce daily. I am using the following code:

VB:

Set pt = ActiveSheet.PivotTables("PivotTable1") 
 
Set pf = pt.PivotFields("Setld Date") 
 
pf.PivotItems("28/10/2010").Visible = False 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



(I plan on replacing 28/10/2010 with a variable once I get this working)

Whenever I run the code I get the following error: Unable to get the pivot items property of the pivot field class

Any ideas on how to solve this problem would be great!