Free Microsoft Excel 2013 Quick Reference

Pivottable events

Dear All,

Please can you help?

I have a pt which I am refreshing through code, the length of which changes each time so I get the mesage "Do you want to replace........". I want to programmatically answer "yes" to this question but neither application.displayalerts or send keys (placed anywhere or everywhere!) appear to work.

No worries I thought, I will just make sure that the contents of the worksheet are clear before the pt refresh so included the ClearContents code in the worksheet_Change and Worksheet_PivotTableUpdate events but putting breakpoints on both of these sees the dialog appear before the event is processed.

Please can you advise where best to put this code or how best to solve my problem? The Worksheet selectionChange event works well for the first time the user selects the pt pagefield (as the user must select the dropdown) but after that, changing the pt page does not fire the SelectionChange event.

I could clear the whole sheet and reconstruct the pt but this just seems a lot of effort for a trivial problem!

Thanks in anticipation


Post your answer or comment

comments powered by Disqus
Dear Forum,

Please can you help with the following problem which has had me stumped for the past day?

I hope I can explain it sufficiently for you to give me guidance on the correct way forward? Here goes..

I have created a pivotchart and placed it on (say) sheet "GRAPH". Excel has created the associated pivottable and stored it on (say) sheet "PIVOT".

Because the formating of the graph is lost each time the pivotgraph is refreshed i.e page fields changed, i have written a small macro to reformat it which i have put in the "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" event in the "PIVOT" sheet. All appeared to work well.

However, on closer investigation, i noticed that the pivotchart wasn't being updated (even though the source pivottable was set to refresh on open - another problem for another day??) so on the pivotchart sheet i had to press the little red exclamation mark to update it and the chart changed before my eyes. I thought I would then record a macro to see what i had to do.

The code the macro recorder gave was "ActiveChart.PivotLayout.PivotTable.RefreshTable" so i simply inserted this into the chart.activate event of the "GRAPH" sheet and assumed it would kick in everytime the graph was activated.

However, it crashes everytime with an error message "unable to get the pivotfields propety of the pivottable class". If I put an msgbox in the relevant "Sub Worksheet_PivotTableUpdate" to display the, its blank!

I've tried all sorts of work arounds like trying to update the pivottable in the graph activate event:-
dim pt as pivottable : set pt = ......... : pt.refreshtable etc
select the sheet before hand etc, etc but no joy.

Following the code through debug it always crashes with the same message. If I manually update the pivottable on the PIVOT sheet (right click "refresh data") the table gets refreshed which then triggers my graph reformat code and everything is fine.

I'm sure i'm doing something wrong but it doesn't appear to like the chart and table being on different sheets?

I hope you have followed my words above but essentially my question is how can i update my pivotchart (through code) based on a pivottable on another sheet?

I really look forward to your replies.

Thanks in anticipation,



Good day!

The goal is to calculate the duration of updating process*of PivotTable. PivotTable can be updated by the button (Refresh) or by simple manipulation (tuning filters, for example). To get the time during which the PivotTable is being updated, you need to know the start time and the end time of the update. When updating is finished the event Worksheet_PivotTableUpdate is raised. But for beginning of the update there is no such events, like BeforeRefresh for QueryTable.

Question: how to define the start of the updating process of*PivotTable?

Excel 2007/2010, pivottable based on external sources (PivotCache.OLEDBConnection).

I would be grateful for any idea! And even for the words - "you can not do that!"

Good day!

The goal is to calculate the duration of updating process*of PivotTable. PivotTable can be updated by the button (Refresh) or by simple manipulation (tuning filters, for example). To get the time during which the PivotTable is being updated, you need to know the start time and the end time of the update. When updating is finished the event Worksheet_PivotTableUpdate is raised. But for beginning of the update there is no such events, like BeforeRefresh for QueryTable.

Question: how to define the start of the updating process of*PivotTable?

Excel 2007/2010, pivottable based on external sources (PivotCache.OLEDBConnection).

I would be grateful for any idea! And even for the words - "you can not do that!"

I am using a pivottable and chart to display data.

The data in the table is displayed like this:

(column A is "date" and columns B through D hold total numbers for three different types of event per day - so col B for Event 1, col C for Event 2 and col D for Event 3)

I'm displaying this data in a pivotchart, but as time passes, and the data increases, the chart has become so crowded it's unreadable.

What I need to do is select the last 30 days worth of data and just display that.

The last 30 records in the table isn't as useful, as you can see - on some days there are aren't any events at all, so no record, which means going back 30 records will take the chart back further than 30 days.

Is there a simple way of telling excel to only just data if it falls between "TODAY-30 and TODAY"?

Any help you offer would be greatly appreciated!

Dear All,

Please can somebody help with my problem? I know it looks really boring to read this lot through but I would really appreciate your advice.

I am having significant problems with one of my spreadsheets (causes Excel to repeatedly crash) since I tried to automate it using userforms.

Unfortunately I can't post the actual spreadsheet due to file size limitations plus the fact that it contains queries to external datasources so I will try and describe it as best I can in pseudo-code. Hopefully someone can see the errors of my ways?

Failing that, I am trying to reconstruct my problem with a cut-down version of the spreadsheet so possibly I might be able to post something in the future.

Here is my scenario:-
1. workbook, containing 3 sheets opens with "sheet1" selected. Sheets 2 & 3 are hidden.
2. simple userform containing 1 combobox and 1 command button display as part of workbook_open() event. Combobox takes as its rowsource a range from sheet1
3.on pressing command button, userform is hidden (not unloaded), macro generates a dynamic SQL statement (using contents chosen from combobox), unhides "sheet2", selects "sheet2" which contains a querytable, updates the command text using the dynamic SQL, refreshes the querty with background refresh = false (i.e. query is completed before macro continues), re-hides "sheet2"
4. macro continues (still originating from the command button on userform), unhides "sheet3", selects "sheet3" which contains a pivottable, using the results of "sheet2" as its datasource.
5. The worksheet_activate() event of sheet3 includes code to refresh the pivot table so as soon as the sheet is selected the PT is refreshed "before the users eyes" with the new data.
6. On pressing the save button, the workbook_beforeSave() event pops up another userform containing a single textbox & command button. The user types password in textbox and command button checks this passoerd. If OK, saves file else informs user that file cannot be saved and exits with cancel=true
7. Moving away from "sheet3" to "sheet1" (the only sheet visible) causes "sheet3" to be hidden in worksheet_deactivate() event.
8. Worksheet_activate() event of "sheet1" re-displays userform and the process starts again.

Well, on the face of it, this appears to work well UNTIL the user either trys to close the spreadsheet or presses save (with the correct password) then :-

1. If excel is still open and you try to re-open the speadsheet is crashes with Excel has generated errors etc....
2. If I close Excel and then re-open, it will begin to load the file and then display a "file/path access error" msgbox, with debug sitting on the statement.
3. Comment out all refrences to and re-save the file. Then reload it, and manually run macro - all is fine. Uncomment, save & run = no joy!
4. Closing the spreadsheet with "sheet1" selected seems to help significantly (why?) but still causes it to crash occassionally,
4. I'm using Excel 2003 but have tried it on Excel 2002 with similar (but not so erratic results)
5. Have created new workbook, added new comboboxes, "cleaned" my VBA project etc without success so I dont think its a corruption issue.

I am convinced its something to do with the order I am trying to do things and Excel is getting itself tied up in knots?

I have tried every permutation of enabling/disabling events, moving code from userforms to worksheet events, again without success.

I've checked and double-checked that my events aren't getting caught in a recursive loop which is normally the problem when I get this kind of error, but perhaps the events of the Querytable are conflicting with the PivotTable?

Why is Excel loosing the userform details between sessions even though i can "see" the forms in the project explorer?

Conceptually, am I doing something wrong?

If your reading this, then thank you for staying with it and I look forward to any advice you can give to restore my sanity!




I'm got a pivot table in a sheet called "Pivot" which when the user drills down into the pivot by double clicking it opens up a new sheet with the data. I wanted the event handler to run a macro after the users double click. Got the below code but not working.

Any thoughts ??

 Range, _ 
    Cancel As Boolean) 
    If ActiveSheet.PivotTables.EnableDrilldown And _ 
    Not(Intersect(Target, ActiveSheet.PivotTables(1).TableRange2) Is 
    Nothing) _ 
    Then Call FillColours 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance

VBA Noob


I am trying to run a macro inside a Worksheet_Change event.

    If Not Application.Intersect(Target, Range("Mon_Data")) Is Nothing Then 
        If Target.Value > 0 Then 
            Sub Refresh_PivotTables() 
                 ' Refresh_PivotTables Macro
                 ' Macro recorded 10/1/2008 by JackChappers
                Sheets("Summary - Day").Select 
                ActiveWindow.ScrollColumn = 2 
                ActiveWindow.ScrollColumn = 3 
                ActiveWindow.ScrollColumn = 4 
                ActiveWindow.ScrollColumn = 5 
                ActiveWindow.ScrollColumn = 6 
                ActiveWindow.ScrollColumn = 7 
                ActiveWindow.ScrollColumn = 8 
                ActiveWindow.ScrollColumn = 9 
                ActiveSheet.PivotTables("Summary_Thu").PivotSelect "", xlDataAndLabel, True 
                Sheets("Summary - Week").Select 
            End Sub 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
as you can see, i put the macro within the worksheet_Change event so that, when data is changed on another sheet (target.Value >0), the Macro is run (the macro, by the way, refreshes some Pivot Tables).

The refresh Refresh_PivotTables Macro works on it's own, but when i use it like this, i get the message:

"Compile error:

Ambiguous name detected: Worksheet_Change"


If it makes any difference, i also have another Worksheet_Change event above performing another action. I think that may be the problem, if it is, how do i run them both without the error?


I'm not too familiar on events, but it's my latest area of learning. I have a worksheet with three pivot tables. All three pivot tables have the "Name" PivotField as a Page selection. I've used code similar to what is below to change pivot fields successfully, but it does not appear that this macro is firing.

B12 is where the first pivot table's name field exists. Basicly, I want the user to be able to change the Name in cell B12, and have the other two pivot tables change automatically. I am disabling events because I'd like to have three such macros so any time the user changes any of the pivots, the other two follow suit.

When I change the first pivot table, Name being in B12, I thought this would fire, but it doesn't. Any suggestions? Is there another event I should be using?

    If Target.Address = "$B$12" Then 
        Application.EnableEvents = False 
        If ActiveSheet.PivotTables("Tardy").PivotFields("Name").CurrentPage  Target.Value Then _ 
        ActiveSheet.PivotTables("Tardy").PivotFields("Name").CurrentPage = Target.Value 
        If ActiveSheet.PivotTables("Occ").PivotFields("Name").CurrentPage  Target.Value Then _ 
        ActiveSheet.PivotTables("Occ").PivotFields("Name").CurrentPage = Target.Value 
        Application.EnableEvents = True 
    End If 
End Sub 

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

I'm trying to write code that will run when the user moves
pivot fields between page and row area.

My first idea was to enter a formula:


It's not a guarantee, but most times this would cause the
formula to change. So then I coded the Worksheet_Change
event. Of course this did not work because the Change
event looks for the actual content of the cell to change,
not the value of the formula. So I put my code in a
Worksheet Calculate event. This works, but the event will
run ANYTIME something on the sheet calculates. I don't
know any code that would limit the event to a range.

So... what can I do (Excel 2000 or older) to have code run
only when the pivot table is pivoted.


I've created the following Calculated Field formula in my PivotTable to
capture the number of events that were late. DaysLate is the difference
between the target completion date and the actual date completed.


The formula works great and places a 1 where appropriate. However, the
Grand Total is incorrect in that is always displays 1 also instead of
totaling the column.

Any ideas?

Posted: Thu Apr 27, 2006 7:23 pm Post subject: Example of a Event Handlers for pivot Tables


Hi all,

Think i've cracked it. My macro is called Fillcolors


Option Explicit
Public mSheet As String

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, _
Cancel As Boolean)

Dim curCell As String, ptname As String, a As Integer

If ActiveSheet.PivotTables.Count = 0 Then GoTo NoPT

On Error GoTo NoPT
If IsEmpty(Target) And ActiveCell.PivotField.Name <> "" Then
Cancel = True
End If
mSheet = ActiveSheet.Name
curCell = ActiveCell.Address
ptname = Sh.Range(curCell).PivotTable

If ActiveSheet.PivotTables(ptname).EnableDrilldown Then
Selection.ShowDetail = True
Call FillColors
mSheet = ActiveSheet.Name

On Error GoTo 0

End Sub


VBA Noob


Looking for excel to activate a macro when a user double clicks on a pivot table to drill down into what makes up the total. Think I need something like the beliow to call my macro called FillColours.

Does anyone have any thoughts ??


Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, _
Cancel As Boolean)

If ActiveSheet.PivotTables.EnableDrilldown = True Then Call FillColours

End Sub

VBA Noob

Hi All,

I have a Questionnaire with 2 parts (Part A and Part B), and each of the two parts have the exact same questions (31 questions each). Essentially, the 31 questions in Part A ask the user "the *importance* of each issue", while the similarly worded 31 questions in Part B asks the user "how well you think we are doing".

The scoring (or answers prompted of the user is a scale of 1 to 5, with 1 being HIGH IMPORTANCE/EXCELLENT/STRONGLY AGREE" and 5 being LOW IMPORTANCE/POOR/STRONGLY DISAGREE.

Now, what I'd like to obtain is the "gap" between the "importance" portion of each answer vs. the "how well we are doing" portion of the same answer, from Part B lower down.

On my rawdata sheet, and pertaining to just one submission form, the way the raw data is laid out is as follows (without the pipe "|" characters of course);
Part | Q. No. | Question | Answer
A | 1 | Administration (co-ordination of staff etc.) | 1
A | 2 | Music | 3
A | 3 | Traditional Service | 2
A | 31 | Social Events | 3
B | 1 | Administration (co-ordination of staff etc.) | 4
B | 2 | Music | 1
B | 3 | Traditional Service | 2
B | 31 | Social Events | 4

So essentially what I've done is I've built a PivotTable with the question number and the question in the "row labels" area, and the "Part" in the "column labels" area, and finally the answer in the "values" area.

So the way the data shows in the PivotTable is:
Q. # | Questions | A | B |
1 | Administration (co-ordination of staff etc.) | 1 | 4 |
2 | Music | 3 | 1 |
...and so on.

So from the PivotTable above (or alternatively if there is a better/different way to do this) what I'd like to obtain is....for each question, I'd like to highlight/caclulate the gap between the importance to the user, and how well we are doing in that area.

So example row/question # 1 in the pivottable above says that it's of high importance to the user (Part A value/answer = 1), where as we are doing very poor in that area (Part B value/answer = 4). So there is a gap of 3, and that should be red-flagged, for us to take action on.
A reverse of that i.e. row/question # 2 would be that the question is of low importance to the user (Part A=3), however we are doing Excellent in that area (Part B=1)...and that's a green flag right there.

Now, the question I have is this: for each question/row in the PivotTable, how can I get the gap/difference between the value under Part A, and the value shown under Part B?

Again, if a PivotTable is the wrong approach then appreciate letting me know how to approach this problem.



Hi, and thanks in advance for any help!

I'm trying to basically replace the selection box on a PivotTable, i.e. the box with the check marks you get when you click the down arrow on a PivotField. Essentially, I want to trap the mouse click on the arrow for the PivotField and display my own UserForm. I'm hoping this is somehow possible, although it seems like it might be a struggle.

Will this require API calls? I have almost no experience with API calls, so any help or a reference to get me started would be appreciated as well.

Thanks again!

Edit: I'm using Excel 2003 under Windows XP Pro.

Hello all,

I have a table as follows:

Column A: Time of day increasing by seconds (i.e. A2=12:00:01, A3=12:00:02, A4=12:00:03 etc.)
Column B: a statement about an event, for example for a light switch. It is either ON or OFF for periods of time. So cells Bx to By will all be "OFF" where as cells By+1 to Bz will all be "ON" and then again OFF and ON. However, the duration of the OFF and ON events changes every time, i.e. they do not last the same, they vary every single time.

What I'm looking to do:
I'm looking to calculate the duration of the "ON" events. I want to find out how long the light switch was ON, each time it was turned ON (not the aggregate total, but for each event).

Any ideas how I could go about doing this? I can't think of a formula, and the result would look something like this (I guess):

Column X // Column Y (Start) // Column Z (end)
ON // time // time

Perhaps a pivot table. I think I may need to convert the ON and OFFs into numerics (say 1 and 0) and somehow use a pivottable but I can't exactly figure out how.

Any ideas will be much appreciated.

I'm having trouble with using workbook events to fire a simple macro that deletes a couple of worksheets and refreshes a pivot table.

The scenario is that an accounting application I'm using spools reports into excel, specifically onto a template that i've setup to include a pivot table with a dynamic named range. THe application outputs rows of data onto one sheet i'm then using the workbook close event to trigger a macro which (should!) refresh the pivot table and delete some unneeded sheets. I know the macro fires because the unneeded sheets are deleted but the pivot table doesn't refresh.
Using exactly the same code outside of the scenario of the accounting application it works perfectly.

A) What's going on?

B) More importantly what can I do about it? is there another way to refresh the pivot table other than the code i'm using that might work?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
'this checks if the data has spooled in from the accounting application
If Not Sheets(1).Range("A8").Value = "" Then

    If WorksheetExists("FLAG") Then
        'this should refresh the pivot table         
        Dim pvtTable As PivotTable
        Set pvtTable = Worksheets("Pivot").Range("B5").PivotTable

        Application.DisplayAlerts = False
        Application.DisplayAlerts = True
    End If
End If

On Error GoTo 0

End Sub

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean

On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0

End Function
As I say the code does work, its just not refreshing the pivot table specifically when the accounting application works with it - I can take the output report, open and close it and the pivot table will refresh, though as I say the macro is firing because the worksheets get deleted.

I have the following short macro that I want to put in a worksheet activate

Selection.AutoFilter Field:=3, Criteria1:="#N/A"

I keep getting Autofilter method of range class failed.

What do I need to change?

Barb Reinhardt

I work with pivottables in my code.
It would be helpful to show me some snippet example code for the following :

a sheet with flat data (columns & rows) is the source of the pivottable. One column is a bit like a datafield (it is text).

On the event loading the userform he should read this column and this data feed a combobox. Only unique fields (eg. 200701R1 appears twice) are required.

1. How to get, loop the data ? Should I put them in an array .
2. How to get only unique results (distinct row)
3. How to feed the combobox.

snippets are ok, if I see the direction, I work it out.

thanks in advance.

Small bit of help needed for 2 items.
I have a pivot table, which I want to have a user type in a value in cell
B3. 1) If the value is found in the drop down list, change the pivot table,
2) if not, display a message box saying so.
1) This works, sort of, but the code just keeps on running.
2) I haven’t got here yet but any suggestions would be welcome.


Private Sub Worksheet_Calculate()
Worksheet_Change Range("VB_Trigger") 'Named range on Sheet Pivot-Table",
location B3
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim pt As PivotTable
Dim pfStockNumber As PivotField 'The PAGE FIELD
Dim strSN As String 'Named range on Sheet Pivot-Table",
location B3
Set pt = ActiveSheet.PivotTables(1)
Set pfStockNumber = pt.PivotFields("STOCK#")
Application.ScreenUpdating = False
strSN = ActiveSheet.Range("B3").Value

' Set up the event to watch B3 single cell.
If Target.Address = Range("VB_Trigger").Address Then

ActiveSheet.PivotTables("PivotTable1").PivotFields("Stock#").CurrentPage =
MsgBox "Stock Number Not Found" 'I haven't worked on this part yet.
End If

Application.ScreenUpdating = True
End Sub

Is it possible to trigger an event on vba when a user drills down (or back)
in a pivot table. Is there anything like: ?

sub PivotTable.OnDrillDown

End Sub


First post ever.

I have a macro (Excel 2003) which refreshes a number of pivot tables based on values in three different cells on a separate worksheet. The macro runs fine when fired independently.

I added a dropdown list to select values for each of these cells and triggered the firing of my macro using a worksheet change event. This works perfectly.

What I really want is to use three combo boxes to select the values and to trigger the macro using a combobox change event. When I try this, however the macro bombs on the third line of code. I'm pretty new to VBA and I've never tried to use the combobox change event so I may be missing something really basic.

Here's the code I'm using for the change event. (The macro name is "RefreshPivots"). I've placed the following code on the worksheet object (within the VBA editor) for the worksheet where the combo box was placed. Again the macro runs perfectly when I run it on its own which leads me to suspect that there's something wrong with my combox change event code.

            End Sub
Here's a snippet of the macro code which refreshes the first of several pivot tables:

'Refresh pivots
'  This block refreshes the pivot cache of all pivot tables to deal
'   with problems where drop-down list for Page field "SPECIALTY" was getting
'   corrupted dropping items from list and adding an item "0" in the pivot table
'   Page field selection list. Code rebuilds the
'   pivots by
'       1) Removing items from the "Page" dimension of each pivot table
'       2) Refreshing the pivot cache
'       3) Adding the Page fields back
'       4) Supplying the user selected values for each of the three page fields
'    **********************************************************************
'    **********************************************************
'    ****                                                              ****
'    ****   1   Refresh Pivot Table:                          ****
'    ****       Filled_2_yrs_ago_Counts_by_Month   ****
'    ****       (Worksheet: Filled Reqs - Counts       ****
'    ****                                                              ****
'    **********************************************************
'    **********************************************************

'               ***************************************************
'   *************  (a) Refresh Cache
'               ***************************************************
    Sheets("Filled Reqs - Counts").Visible = True
    Sheets("Filled Reqs - Counts").Select
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("MED_CTR"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("RECRUITER"). _
        Orientation = xlHidden

    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotCache.Refresh ''

    With ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY")
        .Orientation = xlPageField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("MED_CTR")
        .Orientation = xlPageField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("RECRUITER")
        .Orientation = xlPageField
        .Position = 1
    End With

'               ***************************************************
'   *************  (b) Refresh Pivot with user-selected values
'               ***************************************************
   ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY"). _
        CurrentPage = Range("A1").Value

   ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("MED_CTR"). _
        CurrentPage = Range("A2").Value

   ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("RECRUITER"). _
        CurrentPage = Range("A3").Value

The line of code (executable line3) where the macro fails is:
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY"). _
        Orientation = xlHidden
The error is:

Run-time error '1004'
Unable to get the PivotFields property of the PivotTable class

Thanks for any help you can offer


In some worksheet there are 3 sheets in which pivot tables exists i am writting a code in pivottable update event in every sheets. When opened it is getting fired and now i need to set focus to Sheet1.
I should have a event that will fire after all pivot refresh.


See Chapter 8, page 146: Automatically Updating a Range Name Reference.
Step 2: Add a VBA Event to automatically refresh the PivotTable report
1. Press Alt+F11, and then double-click the sheet name in the VBAProject pane.
2. From the left dropdown list above the Module sheet, select Worksheet, and from the right dropdown list, select Activate.
3. Copy the code below and paste it into the Module sheet.

End Sub

4. Press Ctrl+S to save the workbook, and then press Alt+F4 to close the VBA. The PivotTable report is refreshed automatically upon selecting the sheet that contains it.

I need to determine a cells value (an ID record item number) within a row being deleted by the user before completion of the 'delete-row' event so that it can use the ID to reference corresponding data in another sheet and delete that also.
Eg. if I have sequencial ID numbering of 1 - 20 in range A1:A20 and then delete row 10 then the ID number of 10 in A10 should be assigned to (IDName) variable.
Ive tried to use the following method but it only returns the resulting target row after the row thats been deleted and returns the incorrect target value
Is there such an event as..
Sub Worksheet_BeforeDeleteRow(ByVal Target As Range)

    IDName = Cells(Target.Row, 1).Value 
    If Target.Cells.Count = Cells.Columns.Count And IDName  "" Then 
        RemoveOtherRowEntries (IDName) 'passes the ID value to the next routine to delete matching records of the same ID
    End If 
End Sub 

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


First time poster, fair warning!

I'm working on some VBA code that will allow a user to specify what fields they want to see in a PivotTable, then create the PivotTable on the spot on a new worksheet.

Presently, my code works just great when it comes to creating the pivot and pulling in the specified fields. However, I run into an error when I try to have the code format the table in a certain way.

Specifically, I would like the pivot table to have the following columns - "Year," then "Quarter," then the name of the specified field. The pivot table generates this layout fine if there are at least two values being pulled in - if there is only one data field for the pivot table, it kicks back the error message "Run-time error '1004': Unable to set the Orientation property of the PivotField class."

Here is the code I'm trying to use to set up the formatting :

ActiveSheet.PivotTables("PTAY" & counter).ShowTableStyleRowStripes = True 
ActiveSheet.PivotTables("PTAY" & counter).TableStyle2 = "PivotStyleLight1" 
ActiveSheet.PivotTables("PTAY" & counter).PivotSelect "", xlDataAndLabel, True 
ActiveSheet.PivotTables("PTAY" & counter).RowAxisLayout xlOutlineRow 
ActiveSheet.PivotTables("PTAY" & counter).PivotFields("Yr").Subtotals = Array(False, _ 
False, False, False, False, False, False, False, False, False, False, False) 
ActiveSheet.PivotTables("PTAY" & counter).PivotFields("APASeg").ShowDetail = False 
With ActiveSheet.PivotTables("PTAY" & counter) 
    .RowGrand = False 
End With 
With ActiveSheet.PivotTables("PTAY" & counter).DataPivotField 
    .Orientation = xlColumnField 
    .Position = 3 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Specifically, that last block of code, where it tries to set .Orientation to xlColumnField and .Position = 3. I included the rest to hopefully give ya an idea of the rest of the formatting.

If I disable those 4 lines, Excel generates a functional pivot table, but the name of the selected field appears at the top left of the pivot, instead of as a header along the columns. I could use this format without too much issue, but I would like to maintain the column headings.

I know the code looks messy, sorry! I've just been tweaking it as I go. I'm thinking I'll clean it up with a "With ActiveSheet.PivotTables ..." at some point once everything is happy and working together.

Any guidance or tips would be great. Thanks for reading, hope you're having a great day!

No luck finding an answer? You could always try Google.