Code assistance to existing code to create pivot to group data by YEAR and QUARTER
I have code which creates a pivot table. The code generates the first stage of creating the pivot table. After the pivot has been partially creating, I want to include code which will group the table by YEAR and QUARTER. I tried recording the steps as a macro and copying to existing code but didn't work. The correct is basically the last 3 lines of code below that needs to be adjusted.
In addition, I would also like to set column totals and grand totals to false. The name of the macro is "CreatePivot_NewMethod".
The attached workbook includes a tab 'Desired Output' which is what I want the end result to display. Any assistance is appreciated.
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Re: Code assistance to existing code to create pivot to group data by YEAR and QUARTER
I would love to use Power Query but I have more than a dozen co-workers who need to use this document and don't have the resources to teach them all how to use Power Query. I really need a solution for VBA as per upper management. Thanks
Re: Code assistance to existing code to create pivot to group data by YEAR and QUARTER
Are you aware that once the query is written, any changes to the source document will be automatically updated by selecting Refresh All on the Data Tab.
Re: Code assistance to existing code to create pivot to group data by YEAR and QUARTER
I wasn't aware that changes to source document will update automatically. There is still other steps I'm required to do after I get this part working, which I haven't included in requirements/request (e.g., I will need to convert to values/non-pivot to). Would you still be able to suggest corrections I can make to VBA code to group by QUARTER and YEAR? I really need the VBA code and will play around with Power Query at a later date? Thanks
Re: Code assistance to existing code to create pivot to group data by YEAR and QUARTER
VBA solution.
Not using Pivot sheet.
Result is in sheet "Result", get data from sheet "Production" directly.
Clic the button "Group data with year-Quater"
PHP Code:
Option Explicit Sub QuarterGroup() 'This code suppose that the production data are sorted with date in column A Dim lr&, i&, k&, rng, res(1 To 100000, 1 To 4), id As String Dim dic As Object, key, val, sp Set dic = CreateObject("Scripting.Dictionary") With Sheets("Production") 'save source data into array "rng" lr = .Cells(Rows.Count, "A").End(xlUp).Row rng = .Range("A2:D" & lr).Value End With For i = 1 To UBound(rng) id = Year(rng(i, 1)) & "Qtr" & Int((Month(rng(i, 1)) - 1) / 3) + 1 ' = 2021Qtr1 or 2023Qtr4... If Not dic.exists(id) Then val = rng(i, 2) & "|" & rng(i, 3) & "|" & rng(i, 4) dic.Add id, val Else sp = Split(dic(id), "|") dic(id) = sp(0) + rng(i, 2) & "|" & sp(1) + rng(i, 3) & "|" & sp(2) + rng(i, 4) End If Next For Each key In dic.keys sp = Split(dic(key), "|") If Left(key, 4) <> id Then id = Left(key, 4): k = k + 1: res(k, 1) = id End If k = k + 1: res(k, 1) = Right(key, 4): res(k, 2) = sp(0) res(k, 3) = sp(1): res(k, 4) = sp(2) Next Sheets("Result").Activate Range("A1:D10000").ClearContents Range("A1:D1").Value = Sheets("Production").Range("A1:D1").Value Range("A2").Resize(k, 4).Value = res With Range("B:D") .NumberFormat = "0,000.00" .EntireColumn.AutoFit End With End Sub
Bookmarks