pominoz
05-20-2013, 06:02 PM
Hi there,
I have managed to write (or hobble together, more accurately) the VBA to create a pivot table from my 'Raw data' worksheet. (spreadsheet attached) the code is below.
What I would really like to happen is that when the macro is run a form is presented which asks which type of report is required:
Year to date - this would be financial year (Australia) which is July 1st to June 30 - so the next financial year would be July 1 2013 - June 30 2014 - The form would present a list of all financial years that are listed in 'Raw data' column A - this could be selected on its own as the highest level of report.
Quarter to date - this would be quarters from July 1 onward so Jul, Aug, Sep = Q1 etc - the form would present a list of all Quarters available in the selected Year to date
Month to date - the form would present a list of months available based on the selection of year to date and quarter to date.
The macro should then create the appropriate pivot table and name the pivot worksheet accordingly - 2013-2014-Q1-Mar as an example
If the report has previously been run (the worksheet already exists) then the macro should delete the original and replace with the new.
I have attached an example spreadsheet and I really hope that one of the many gurus out there can help me with this.
Thanks in advance. Pominoz
Sub Create_Report()
'Creates a pivot table in a new worksheet with Year, Quarter & Month Page filters
Dim SourceSht As Worksheet
Dim NewSht As Worksheet
Dim pt As PivotTable
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set SourceSht = ActiveSheet
Set NewSht = Sheets.Add
Set pt = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="'Raw data'!R1C1:R10000C20").CreatePivotTable(TableDestination:=NewSht.Range("A3"), DefaultVersion:=xlPivotTableVersion10)
With pt.PivotFields("Month")
.Orientation = xlPageField
.Position = 1
End With
With pt.PivotFields("Quarter")
.Orientation = xlPageField
.Position = 2
End With
With pt.PivotFields("Year")
.Orientation = xlPageField
.Position = 3
End With
With pt.PivotFields("Consultant")
.Orientation = xlRowField
.Position = 1
.PivotItems("(blank)").Visible = False
End With
With pt.PivotFields("Contract Sale")
.Orientation = xlDataField
.Function = xlSum
.Caption = "Contract Sale Value"
.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""_-;_-@_-"
End With
With pt.PivotFields("Perm Sale")
.Orientation = xlDataField
.Function = xlSum
.Caption = "Perm Sale Value"
.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""_-;_-@_-"
End With
With pt.TableStyle2 = "PivotStyleMedium4"
End With
ActiveWorkbook.ShowPivotTableFieldList = False
NewSht.Select
NewSht.Name = "Pivot Table"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
I have managed to write (or hobble together, more accurately) the VBA to create a pivot table from my 'Raw data' worksheet. (spreadsheet attached) the code is below.
What I would really like to happen is that when the macro is run a form is presented which asks which type of report is required:
Year to date - this would be financial year (Australia) which is July 1st to June 30 - so the next financial year would be July 1 2013 - June 30 2014 - The form would present a list of all financial years that are listed in 'Raw data' column A - this could be selected on its own as the highest level of report.
Quarter to date - this would be quarters from July 1 onward so Jul, Aug, Sep = Q1 etc - the form would present a list of all Quarters available in the selected Year to date
Month to date - the form would present a list of months available based on the selection of year to date and quarter to date.
The macro should then create the appropriate pivot table and name the pivot worksheet accordingly - 2013-2014-Q1-Mar as an example
If the report has previously been run (the worksheet already exists) then the macro should delete the original and replace with the new.
I have attached an example spreadsheet and I really hope that one of the many gurus out there can help me with this.
Thanks in advance. Pominoz
Sub Create_Report()
'Creates a pivot table in a new worksheet with Year, Quarter & Month Page filters
Dim SourceSht As Worksheet
Dim NewSht As Worksheet
Dim pt As PivotTable
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set SourceSht = ActiveSheet
Set NewSht = Sheets.Add
Set pt = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="'Raw data'!R1C1:R10000C20").CreatePivotTable(TableDestination:=NewSht.Range("A3"), DefaultVersion:=xlPivotTableVersion10)
With pt.PivotFields("Month")
.Orientation = xlPageField
.Position = 1
End With
With pt.PivotFields("Quarter")
.Orientation = xlPageField
.Position = 2
End With
With pt.PivotFields("Year")
.Orientation = xlPageField
.Position = 3
End With
With pt.PivotFields("Consultant")
.Orientation = xlRowField
.Position = 1
.PivotItems("(blank)").Visible = False
End With
With pt.PivotFields("Contract Sale")
.Orientation = xlDataField
.Function = xlSum
.Caption = "Contract Sale Value"
.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""_-;_-@_-"
End With
With pt.PivotFields("Perm Sale")
.Orientation = xlDataField
.Function = xlSum
.Caption = "Perm Sale Value"
.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""_-;_-@_-"
End With
With pt.TableStyle2 = "PivotStyleMedium4"
End With
ActiveWorkbook.ShowPivotTableFieldList = False
NewSht.Select
NewSht.Name = "Pivot Table"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub