A macro to create pivot table
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
Last edited by WorldBridge; 11-21-2011 at 05:26 PM. Reason: more appropriate title
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
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Re: A macro to collapse and total rows
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**
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
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks