Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Pivot tables.

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, I am new to coding pivot tables with VBA so please bear with me.....

    I recorded a macro of myself creating a pivot table. Take a look at this line:

    '
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "NetPrem!R1C1:R244C30").CreatePivotTable TableDestination:=Range("A1"), _
    TableName:="PivotTable1"

    Now, the source data thing is annoying me. I don't want to pick the data up from a fixed range. I want to pick up the data from a dynamic range.

    I have tried defning my range such as:


    Set rng = Sheets("NetPrem").Range([a1], [a1].End(xlToRight).End(xlDown))

    or even

    rng = Sheets("NetPrem").Range("A1").Currentregion

    but when I try and put this in the sourcedata thing as

    SourceData = rng
    or
    SourceData = "rng"
    etc
    then it doesn't like it at all.

    Thanks for your help,

    RET79

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this but change the column names to suit.
    (Creates a chart also)

    Public Sub ByRegion()
    'NOTES IN THIS SUB ARE TYPICAL OF ALL SUBS IN THIS MODULE
    'Erase existing worksheet or chart if present.
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("By Region").Delete
    Charts("By Region Chart").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    'Select the input sheet and range A2
    Worksheets("INPUT").Select
    Range("A60").Select
    'Create pivot table
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    Range("A60").CurrentRegion.Address).CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable1"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="NAME", _
    ColumnFields:="M", PageFields:="REGION"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("AMOUNT").Orientation = _
    xlDataField
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of AMOUNT").Function = _
    xlAverage
    ActiveSheet.PivotTables("PivotTable1").Format xlTable4
    'Rename pivot table
    ActiveSheet.Name = "By Region"
    'Move it to the right of the INPUT sheet
    Sheets("By Region").Move After:=Sheets(3)
    'Adjust column width
    Range("B5:M5").Select
    Selection.ColumnWidth = 7
    Range("A1").Select
    'Set up page for printing and add footer
    With ActiveSheet.PageSetup
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .Orientation = xlLandscape
    .LeftFooter = "&B Confidential&B"
    .CenterFooter = "&D"
    .RightFooter = "Page &P"
    End With
    Range("A7").Select
    Do
    Range(ActiveCell, ActiveCell.Offset(0, 13)).Select
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    ActiveCell.Offset(3, 0).Select
    Loop Until ActiveCell.Value = ""
    'Create chart to accompany pivot table
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("By Region").Range("A1")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = "By Region Chart"
    Application.CommandBars("PivotTable").Visible = False
    'Add footer to chart
    With ActiveChart.PageSetup
    .LeftFooter = "&B Confidential&B"
    .CenterFooter = "&D"
    .RightFooter = "Page &P"
    End With
    Worksheets("INTRO").Select
    Application.Calculation = xlCalculationAutomatic

    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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