A macro to create pivot table

[WorldBridge]

I want to consolidate all the rows with identical numbers in column G
but I need column L from all of the consolidated rows be totaled together when they are collapsed.

The attached file shows what I need before and after.

The information in columns F, I and K is not very important for what I need.
F and K will usually be the same value, but column I will almost always be unique for each row.
However this is handled doesn't really matter.

Can this be done with a pivot table? If so, I could really use a macro to turn it into one.

** UPDATE**

I have created a macro to format the information correctly
Now I just need the macro to convert the data to a pivot table

[royUK]

If you arranged the data into a proper Excel table format then you would be able to use a PivotTable or AutoFilter with a SubTotal formula

[WorldBridge]

Thank you, I came to the same conclusion myself.
The issue is that Quickbooks outputs to this format so I have little choice.


I've figured out exactly what I need to do to organize the data how I need.

Now I would like a macro to automate the tasks (it's for someone who's not very comp literate)

Here's a list of the tasks;
Delete column D
Add "Job" title to cell C1
Copy appropriate job title down column C
(C3 copied to cells C4-C24)
Unnecessary rows deleted
(totals and job headings i.e. rows 2-3, 25-28)

Then the sheet should look like "middle step"

Then I'm sure there's an easy macro to convert the data to a pivot table
setup for pivot table;
Report Filter: Date
Column Labels: Source Name
Row Labels: Job
Values: Sum of amount

Finally it would be amazing if it made a freeze pane
(in my example cell B5)

**I've never worked with macros but I'm very computer savvy so I could put several together if need be**

[arlu1201]

As per your last update, you mentioned that you already have the code to automate the steps. Find the code below to create the pivot -

    lrow = Worksheets("middle step").Range("C" & Rows.Count).End(xlUp).Row
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "middle step!R1C3:R" & lrow & "C11", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet3!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet3").Select
    Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Job")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Source Name")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Amount"), "Sum of Amount", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False

[WorldBridge]

I was new to macros when I posted this. The answer was easy enough: I needed to record a macro and then adjust the necessary variables etc...