Solved Macro Driven Pivot Table Process

September 18, 2013 at 17:12:38
Specs: Windows 7
Below is a macro to automate the pivot process, I need to expand the program to include Setting the pivot table to Repeat all lables. When i try to record a macro, the system generates the following "'ActiveSheet.PivotTables("PivotTable5").RepeatAllLabels xlRepeatLabels" however inserting this into the macro does not work. Can anyone provide the correct codeing to accomplish this added step.

Your kind assistance is greatly appreciated. Please not i'm working in Excel 2010.

DiamandDave

Sub create_pivot()

Application.DisplayAlerts = False
'*******************************************************************
On Error Resume Next
Sheets("Demand Pivot").Delete
Dim objTable As PivotTable, objField As PivotField
'*******************************************************************
ActiveWorkbook.Sheets("Demand").Select
Range("A1:V" & Range("A1048576").End(xlUp).Row).Select
Set objTable = Sheets("Demand").PivotTableWizard
'*******************************************************************
ActiveSheet.PivotTables("PivotTable5").RepeatAllLabels xlRepeatLabels

Set objField = objTable.PivotFields("Item Number")
objField.Orientation = xlRowField
objField.Subtotals = False
Set objField = objTable.PivotFields("Sort Name")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Price")
objField.Orientation = xlRowField

'*******************************************************************
Set objField = objTable.PivotFields("Rev Date")
objField.Orientation = xlColumnField
'*******************************************************************
Set objField = objTable.PivotFields("Quantity")
objField.Orientation = xlDataField
objField.Function = xlSum
'*******************************************************************
objTable.PivotFields("Item Number").Subtotals(1) = False

objTable.PivotFields("Sort Name").Subtotals(1) = False
objTable.PivotFields("Price").Subtotals(1) = False
objTable.ColumnGrand = False
objTable.RowGrand = False
'*******************************************************************
ActiveSheet.Name = "Demand Pivot"
'*******************************************************************
Application.DisplayAlerts = True


End Sub


See More: Macro Driven Pivot Table Process

Report •


✔ Best Answer
September 20, 2013 at 09:40:13
Yes, its the only one in the workbook
Yes, i tried ActiveSheet.PivotTables(1).RepeatAllLabels xlRepeatLabels with and without ().
No, i'm not totaly clear on the difference between an objuecs name and its munber, but i can see as i run thru the macro, that the name stays the same, but the number continues to change by adding one.

Doing additional research, i have found a solution by adding the following.

Dim pvtName As String
pvtName = objTable.Name
ActiveSheet.PivotTables(pvtName).RepeatAllLabels xlRepeatLabelsTh
This does solve the problem in that if fixes the everchanging "Pivot Table Name"

Please let me know if i'm totally missing your points. I do appreciate your instructional approach. Sorry i'm still at such a basic level. Your support is greatly appreciated



#1
September 19, 2013 at 13:09:51
I don't do much with Pivot Tables, and I don't have a copy of your spreadhseet to test your code against, so I can't offer much as a direct answer to your question. However, I have a suggestion that might help.

Have you tried to Single Step through your code via F8 to see if the line you added is throwing up an error? You have an On Error Resume Next instruction, which would hide the error if one was occuring.

I suggest that you comment out the On Error Resume Next line and then use F8 to Single Step through the code to see what happens. If everything else works as you expect, except for the RepeatAllLabels line, it might be the placement of the instruction or it might be a problem with the instruction itself. Perhaps the error (if there is one) will point you in the right direction.

One other item: the instruction refers to a specific Pivot Table: "PivotTable5". Are you sure that "PivotTable5" actually exists in the sheet you are running the code against? The macro you recorded will have picked up the name of the Pivot Table at the time you recorded the code. If the code is looking for "PivotTable5" but a differently named Pivot Table now exists in the sheet, that might be your problem (and your error). Again, the On Error Resume Next instruction would allow the code to blow right past that line without telling you if it threw up error.

If that's the problem, you will need to find a way to determine the current name of the Pivot Table - each time the code runs - and use that name in your RepeatAllLabels instruction.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 19, 2013 at 15:08:47
DerbyDad, thanks for your thoughts, Note, when i insert the line "ActiveSheet.PivotTables("Demand Pivot").RepeatAllLabels xlRepeatLabels" i do not get an error message, nothing changes.

as to the placement, i inserted the comand between every line, but in no case did it impact the results.

as to the name, i modified the process to change the name of the pivot table to "Demand Pivot" as shown above to eliminate the naming issue..

Any ideas? would a copy of the program help?

Your support is greatly appeciated, i'm learning from every encounter


Report •

#3
September 19, 2013 at 16:48:48
It is not clear to me if you followed my suggestion to comment out the On Error line and then single step through the code. Did you do that?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 19, 2013 at 17:07:15
Sorry for the confusion, my problem is the comand line i used does not generate an error message. the macro processes, without error, but all command lines generates the desired results except for the duplication of the "item number" command.

I tested the command in everyother line both with "pivotTable5" and the pivotTable renamed.

i'm sorry if there is a basic step I'm missing. because there is no error message its hard for me to determine the point of the problem. Would it help to send a copy of the program?


Report •

#5
September 19, 2013 at 17:45:22
You still have not directly answered the question that I asked you.

I'll try one more time.

Did you open the VBA editor, comment out the On Error Resume Next line and then Single Step through the code using F8 to execute each line one at a time?

That should be a Yes or No answer. If your answer is No, then I'll ask ahead of time: Why not?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
September 19, 2013 at 18:03:04
i guess i need to say no, my level of understanding of programming is limited, your comment now makes more sense (comment out the on error resume next. Let me proceed with your suggestion now that i understand. I will let you know the outcome.

sorry any inconvienence.


Report •

#7
September 19, 2013 at 18:35:52
So, now better understanding of what commenting out "on error resume next" is see why i was not seeing errors.

with commenting out errors. the process froze at the command
ActiveSheet.PivotTables("PivotTable5").RepeatAllLabels xlRepeatLabels

i tryied placing the command at all key points and it failed. I created a command to change the current worksheet to "Demand Pivot" and then changed "PivotTables5" to "Demand Pivot" i tried this at several points in the process. each time the process presented an error at the command.with the following error

Run-time error '1004:
Unable to get the pivottable property of the worksheet class

i can process the command manually, but the command i'm using in excel 2010 does not seem to work.

I appreciate your patience


Report •

#8
September 19, 2013 at 19:51:33
re: I created a command to change the current worksheet to "Demand Pivot" and then changed "PivotTables5" to "Demand Pivot"

The RepeatAllLabels instruction is not looking for the name of the worksheet, it is looking for the name of the Pivot Table.

To explain what I mean, look at this instruction:

ActiveWorkbook.Sheets("Demand").Select

Notice how it is Selecting the sheet by its Name: Sheets("Demand")

Now look at this Instruction:

ActiveSheet.PivotTables("PivotTable5").RepeatAllLabels xlRepeatLabels

Notice how it is referencing a Pivot Table by its Name: PivotTables("PivotTable5")

Changing the name of the Sheet does not change the Name of the Pivot Table.

Whenever an object is created in Excel, Excel gives it a name. It doesn't matter if it's a Sheet, a picture, a shape, a Pivot Table, etc. Every object has a Name and you can access that object within VBA by that Name. You can also access the Object by a number, if you know which Pivot Table it is.

If the Pivot Table that you are trying to access is the only one on the sheet, then it might be Pivot Table #1 and this instruction might work.

ActiveSheet.PivotTables(1).RepeatAllLabels xlRepeatLabels

Notice that I replaced the Name "PivotTable5" with the number 1.

A Name must be a text string enclosed in quotes, but a number is the actual number by which VBA knows the object.

PivotTables("PivotTable1") may or may not be the same object as PivotTables(1). "PivotTable1" is just a Name. You could name that Pivot Table "Fred" or "Sales" or "Revenue" and it would still retain its number within Excel.

Since I can't actually test your code without a copy of your workbook, that is the best I can offer at this point.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
September 19, 2013 at 20:05:20
Thanks for your support, let me test your suggestions.

Report •

#10
September 19, 2013 at 21:35:01
Thanks for the detailed explanation, i did fint the pivot table name in the pivot properties, but the name changed each time the macro was run. Is there a code that can be written to replace whatever is currently in the system with say "PivotTable1"

Report •

#11
September 20, 2013 at 07:38:19
Did you read the part of my response where I said that the Pivot Table can also be referenced by it's number?

I'll repeat what I said:

If the Pivot Table that you are trying to access is the only one on the sheet, then it might be Pivot Table #1 and this instruction might work.

ActiveSheet.PivotTables(1).RepeatAllLabels xlRepeatLabels

Once again, I need you to answer my specific questions with a Yes or No.

Is the Pivot Table that you are trying to access the only one on the sheet? Yes or No

If Yes, did you try the following instruction? Yes or No

ActiveSheet.PivotTables(1).RepeatAllLabels xlRepeatLabels

It is not clear to me if you understand the difference between an object's Name and it's Number.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
September 20, 2013 at 09:40:13
✔ Best Answer
Yes, its the only one in the workbook
Yes, i tried ActiveSheet.PivotTables(1).RepeatAllLabels xlRepeatLabels with and without ().
No, i'm not totaly clear on the difference between an objuecs name and its munber, but i can see as i run thru the macro, that the name stays the same, but the number continues to change by adding one.

Doing additional research, i have found a solution by adding the following.

Dim pvtName As String
pvtName = objTable.Name
ActiveSheet.PivotTables(pvtName).RepeatAllLabels xlRepeatLabelsTh
This does solve the problem in that if fixes the everchanging "Pivot Table Name"

Please let me know if i'm totally missing your points. I do appreciate your instructional approach. Sorry i'm still at such a basic level. Your support is greatly appreciated


Report •

#13
September 20, 2013 at 10:26:06
I'm glad you have found a solution. Good luck with the rest of your project.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question