Results 1 to 1 of 1

Thread: Pivot table help please

  1. #1
    VBAX Regular
    May 2012

    Pivot table help please

    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

    [VBA]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.Name = "Pivot Table"

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    End Sub[/VBA]
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts