1 Step Away From Automation - Guru's Needed

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
My original post to the last open issue I have is below.

http://216.92.17.166/board2/viewtopic.php?t=81131

When rerecording this macro I found something that boggles me. The macro did not follow my commands which I do not understand why. In the code below - I think the macro made assumption.

The steps I took when creating the macro are as follows -
1 Opened pivot table wizard.
2. Went into layout
3. Dragged Item # from Row to pivot field.
4. Dragged Description from Row to pivot field.
5. Dragged Past Due and 21 dates from Data to pivot fields.

Note - I dragged Past Due to pivot field first - then 3/4, which is reverse to what the macro shows.
At this point the layout is completely empty.

6. Dragged Item # from pivot field to Row
7. Dragged Description from pivot field to Row
8. Dragged Past Due and 22 dates seperately from pivot field to Data changing each from Count of to Sum of.
9. Selected finish.


I have found several issues with this macro -
A. It is not following the steps I requested it to do.
B. It apprears that it breaks up the steps rather than completing the task. No where in the macro does it show me pulling Item # & Description off of the layout nor putting them back.
C. The macro orientates, changes caption, & funtion prior to showing position for all pivots which is backward to what the macro should have shown. I moved it before I changed it.
With ActiveSheet.PivotTables("PivotTable1").PivotFields("6/14/2004")
.Orientation = xlDataField
.Caption = "Sum of 6/14/2004"
.Function = xlSum
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 6/14/2004").Position = 23



I know this is long and drawn out - Any idea's would be useful at this point. :oops: :rolleyes:

Sub Macro120()
'
' Macro120 Macro
'
'

'
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Item #", _
"Description"), ColumnFields:="Data"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 6/7/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/31/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/24/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/17/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/10/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/3/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/26/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/19/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/12/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/5/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/29/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/22/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/15/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/13/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/12/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/11/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/10/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/9/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/8/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/5/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/4/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Past Due"). _
Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Past Due")
.Orientation = xlDataField
.Caption = "Sum of Past Due"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/4/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/4/2004"
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/5/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/5/2004"
.Position = 3
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/8/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/8/2004"
.Position = 4
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/9/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/9/2004"
.Position = 5
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/10/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/10/2004"
.Position = 6
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/11/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/11/2004"
.Position = 7
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/12/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/12/2004"
.Position = 8
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/13/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/13/2004"
.Position = 9
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/15/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/15/2004"
.Position = 10
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/22/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/22/2004"
.Position = 11
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("3/29/2004")
.Orientation = xlDataField
.Caption = "Sum of 3/29/2004"
.Position = 12
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("4/5/2004")
.Orientation = xlDataField
.Caption = "Sum of 4/5/2004"
.Position = 13
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("4/12/2004")
.Orientation = xlDataField
.Caption = "Sum of 4/12/2004"
.Position = 14
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("4/19/2004")
.Orientation = xlDataField
.Caption = "Sum of 4/19/2004"
.Position = 15
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("4/26/2004")
.Orientation = xlDataField
.Caption = "Sum of 4/26/2004"
.Position = 16
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/3/2004")
.Orientation = xlDataField
.Caption = "Sum of 5/3/2004"
.Position = 17
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/10/2004")
.Orientation = xlDataField
.Caption = "Sum of 5/10/2004"
.Position = 18
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/17/2004")
.Orientation = xlDataField
.Caption = "Sum of 5/17/2004"
.Position = 19
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/24/2004")
.Orientation = xlDataField
.Caption = "Sum of 5/24/2004"
.Position = 20
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("5/31/2004")
.Orientation = xlDataField
.Caption = "Sum of 5/31/2004"
.Position = 21
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("6/7/2004")
.Orientation = xlDataField
.Caption = "Sum of 6/7/2004"
.Position = 22
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("6/14/2004")
.Orientation = xlDataField
.Caption = "Sum of 6/14/2004"
.Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of Past Due").Position = 1
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/4/2004").Position = 2
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/5/2004").Position = 3
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/8/2004").Position = 4
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/9/2004").Position = 5
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/10/2004").Position = 6
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/11/2004").Position = 7
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/12/2004").Position = 8
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/13/2004").Position = 9
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/15/2004").Position = 10
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/22/2004").Position = 11
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 3/29/2004").Position = 12
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 4/5/2004").Position = 13
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 4/12/2004").Position = 14
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 4/19/2004").Position = 15
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 4/26/2004").Position = 16
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 5/3/2004").Position = 17
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 5/10/2004").Position = 18
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 5/17/2004").Position = 19
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 5/24/2004").Position = 20
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 5/31/2004").Position = 21
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 6/7/2004").Position = 22
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Sum of 6/14/2004").Position = 23
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Down:=0
Range("E20").Select
End Sub
:rolleyes:
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
The macro recorder is not perfect and cannot record everything. Instead of trying to record everything at once, go back and try to record just one part and see what it does. Then record the next part and the next and see what code you get.

Then reply here with what you want to do that you cannot figure out how to do. Regardless of what the macro recorder does or does not do.
 

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
DRJ

I did as you suggested. The recorder did not do as I asked. In the following macro the dates are in reverse order than how I dragged them off. I dragged Past Due off third, then 3/23 ans so on.
Also - ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Item #", _
"Description"), ColumnFields:="Data"
does not show orientation hidden.

I attempted to run this macro right after creating it. It error'ed out as I suspected it to. With the date inverted - the macro checked for the 7/5 first. I purposely did not drag 7/5 into the pivot field.

ActiveWindow.SmallScroll Down:=-141
Range("D7").Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Item #", _
"Description"), ColumnFields:="Data"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 7/5/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 6/28/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 6/21/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 6/14/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 6/7/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/31/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/24/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/17/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/10/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 5/3/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/26/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/19/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/12/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/5/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/2/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 4/1/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/29/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/26/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/25/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/24/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 3/23/2004"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Past Due"). _
Orientation = xlHidden


First I would like to see the pivotfield name go from "Past Due" to a value of x so I would request so many x's not specific pivotfield names in case a date dropped off or one added in the middle.
Second I would like to see the x's dragged off in proper order.


Thank's


Stapuff
 

Watch MrExcel Video

Forum statistics

Threads
1,123,525
Messages
5,602,177
Members
414,510
Latest member
mande358

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top