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.