Dynamic Formating VBA Pivot Table

[matt4003]

Hello Everyone,

I am working on a project to FCST sales. Each month we download a spreadsheet, FCST's are done on months, so each month, one month drops off the list and a new one is added. The problem I have, is I recorded a macro to create the pivot table, well when one month drops off, the code errors our because it can't find the hard coded month....how do I fix this. Here is the code to create the macro, it is still pretty raw, but I am sure someone knows how to help:

Sub FCST ()
Dim LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.UsedRange.Select

Range(Selection, Selection.End(xlDown)).Name = "Pivot_Range"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"=Pivot_Range").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select


With ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer group")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Int.Customer Name")
.Orientation = xlRowField
.Position = 2
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Mat.group/Diameter")
.Orientation = xlRowField
.Position = 3
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Final product")
.Orientation = xlRowField
.Position = 4
End With

Range("D4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Final product"). _
Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Finished Product")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Nick name")
.Orientation = xlRowField
.Position = 5
End With
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Data2")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SAG Fcst. version")
.Orientation = xlPageField
.Position = 1
End With



ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("09.2006"), "Sum of 09.2006", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("10.2006"), "Sum of 10.2006", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("11.2006"), "Sum of 11.2006", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("12.2006"), "Sum of 12.2006", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("01.2007"), "Sum of 01.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("02.2007"), "Sum of 02.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("03.2007"), "Sum of 03.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("04.2007"), "Sum of 04.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("05.2007"), "Sum of 05.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("06.2007"), "Sum of 06.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("07.2007"), "Sum of 07.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("08.2007"), "Sum of 08.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("09.2007"), "Sum of 09.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("10.2007"), "Sum of 10.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("11.2007"), "Sum of 11.2007", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("12.2007"), "Sum of 12.2007", xlSum




Range("E5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Nick name").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("D5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Finished Product"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("C5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Mat.group/Diameter"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveWindow.SmallScroll Down:=-9
ActiveSheet.PivotTables("PivotTable1").PivotFields("Data2").CurrentPage = _
"FC value (fixed curr.)"
Range("A1").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Range("F5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 09.2006")
.NumberFormat = "#,##0"
End With
'With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ShipQty")
' .NumberFormat = "#,##0"

Range("G5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of 10.2006")
.NumberFormat = "#,##0"
End With

With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
Range("A1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Data2")
.PivotItems("FC Allocation").Visible = False
.PivotItems("FC Potential QTY").Visible = False
.PivotItems("FC price (orig. curr.)").Visible = False
.PivotItems("FC quantity").Visible = False
.PivotItems("FC Shipp. QTY").Visible = False
End With
End Sub

Thanks, MATT

[Excelenator]

I haven't tested this but you might try to do the following:

Dim m(15, 1) As String
Dim x As Integer, y As Integer

For x = 0 To 15
    m(x, 0) = Month(DateSerial(Year(Date), Month(Date) + x, Day(Date)))
    m(x, 1) = Year(DateSerial(Year(Date), Month(Date) + x, Day(Date)))
Next x

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(0, 0) & "." & m(0, 1)), "Sum of " & m(0, 0) & "." & m(0, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(1, 0) & "." & m(1, 1)), "Sum of " & m(1, 0) & "." & m(1, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(2, 0) & "." & m(2, 1)), "Sum of " & m(2, 0) & "." & m(2, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(3, 0) & "." & m(3, 1)), "Sum of " & m(3, 0) & "." & m(3, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(4, 0) & "." & m(4, 1)), "Sum of " & m(4, 0) & "." & m(4, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(5, 0) & "." & m(5, 1)), "Sum of " & m(5, 0) & "." & m(5, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(6, 0) & "." & m(6, 1)), "Sum of " & m(6, 0) & "." & m(6, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(7, 0) & "." & m(7, 1)), "Sum of " & m(7, 0) & "." & m(7, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(8, 0) & "." & m(8, 1)), "Sum of " & m(8, 0) & "." & m(8, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(9, 0) & "." & m(9, 1)), "Sum of " & m(9, 0) & "." & m(9, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(10, 0) & "." & m(10, 1)), "Sum of " & m(10, 0) & "." & m(10, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(1, 0) & "." & m(11, 1)), "Sum of " & m(11, 0) & "." & m(11, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(1, 0) & "." & m(12, 1)), "Sum of " & m(12, 0) & "." & m(12, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(1, 0) & "." & m(13, 1)), "Sum of " & m(13, 0) & "." & m(13, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(1, 0) & "." & m(14, 1)), "Sum of " & m(14, 0) & "." & m(14, 1), xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(m(1, 0) & "." & m(15, 1)), "Sum of " & m(15, 0) & "." & m(15, 1), xlSum

[matt4003]

Hello Excelenator,

Thank you for the response!

This seems to be for a worksheet...but I need code that will execute with the macro. Do you think there is some kind of if/then statement that will evaluate if each of the conditions exist and if it doesn't if won't error out and look for the next one??

thanks!

Matt

[Excelenator]

The code that I posted was directly out of the code that you posted for the pivot table.

[matt4003]

Hello Excelenator,

Perhaps I don't know how to use it then, because the directions say to post it in the worksheet, but I need it in the module. Can you post how the full code would look? I tried what you have, but it errors out on me when I introduce the variables.

How do you do a basic "If this exits, then format, if not, move on?"

Thanks,
Matt