Pivot Table Macro Needed

**timbo1957** · 10-20-2014, 10:03 AM

Hi

I have a couple of pieces of code from other macros and I want to combine them to make one piece of code if possible.

I want to create a pivot table in a new worksheet using the data in the Sheet Report1.

The data starts in A2 and goes across to Q2 and the end of the data varies.

I have this which will count the rows in column A.

    Lr1 = Range("A" & Rows.Count).End(xlUp).Row

I have then amended that to take off the header and sum columns.

    Lr1 = Lr1 - 2

Then I have this to select the range.

    With Range("A2:Q" & Lr1)

I want to put this in here but don't know how to.

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Report1!R1C1:R6826C18").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable2", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("Imprint", _
        "DAC Market", "Data")
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Net Val")
        .Orientation = xlDataField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Margin")
        .Orientation = xlDataField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Home Return")
        .Orientation = xlDataField
        .Caption = "Sum of Home Return"
        .Function = xlSum
    End With
    ActiveCell.Offset(2, 2).Range("A1").Select
    With ActiveSheet.PivotTables("PivotTable2").DataPivotField
        .Orientation = xlColumnField
        .Position = 1

    End With

Basically getting rid of the hard coding of the range in the pivot table part.

Any help much appreciated.

**romperstomper** · 10-20-2014, 10:14 AM

Do you mean your headers are in row 2 on the data sheet? Why are you subtracting 2 and not 1 from the last row variable?

**timbo1957** · 10-20-2014, 10:17 AM

My headers are in row 1 and the total for the sheet is in the very last row which is variable. So I thought I needed to take off 2.

**romperstomper** · 10-20-2014, 10:22 AM

Nope - you need to include headers for the pivot table to use as field names. So:

    Dim LR1                   As Long
    Dim PT                    As PivotTable

    LR1 = Range("A" & Rows.Count).End(xlUp).Row - 1

    Set PT = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                "Report1!R1C1:R" & LR1 & "C17").CreatePivotTable(TableDestination:=Sheets.Add.Cells(1, 3), _
                DefaultVersion:=xlPivotTableVersion10)

    With PT
        .AddFields RowFields:=Array("Imprint", "DAC Market", "Data")
        With .PivotFields("Net Val")
            .Orientation = xlDataField
            .Position = 1
        End With
        With .PivotFields("Margin")
            .Orientation = xlDataField
            .Position = 2
        End With
        With .PivotFields("Home Return")
            .Orientation = xlDataField
            .Caption = "Sum of Home Return"
            .Function = xlSum
        End With
        With .DataPivotField
            .Orientation = xlColumnField
            .Position = 1
        End With
    End With

Note I changed the C18 to C17 since you said column Q, not R.

**timbo1957** · 10-20-2014, 10:31 AM

Hi romperstomper,

This works almost perfectly. How can i make the pivot table start in A1 of the new sheet? It is spread across C:G.

Regards

Tim

**romperstomper** · 10-20-2014, 10:47 AM

You need to change this bit:

Sheets.Add.Cells(1, 3)

You can switch it to:

Sheets.Add.Range("A1")

**timbo1957** · 10-20-2014, 11:00 AM

Brilliant, many thanks.

Pivot Table Macro Needed

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Pivot Table Macro Needed

Re: Pivot Table Macro Needed

Re: Pivot Table Macro Needed

Re: Pivot Table Macro Needed

Re: Pivot Table Macro Needed

Re: Pivot Table Macro Needed

Re: Pivot Table Macro Needed

Thread Information

Users Browsing this Thread

Similar Threads

complex macro needed for pivot table

Macro Help Needed - Link Pivot Table Filter to Regular Table AutoFilter

Macro needed for pivot table

Help needed with Pivot table macro to remove data

[SOLVED] Macro Needed for Pivot Table

Bookmarks

Bookmarks

Posting Permissions