jmac001
asked on
Pivot Table Grouping and Filtering
Hi,
First time creating pivot table using VBA and was wondering if you could help. I would like to group on Month & Year and I am getting a run time error 1004 Group method of Range class failed - not sure how to troubleshoot.
Secondly how would I add additional criteria to where I have the xlPageField defined? For status I need to see "new" and for Scope need to exclude "hld,0,ded".
Here is the code
First time creating pivot table using VBA and was wondering if you could help. I would like to group on Month & Year and I am getting a run time error 1004 Group method of Range class failed - not sure how to troubleshoot.
Secondly how would I add additional criteria to where I have the xlPageField defined? For status I need to see "new" and for Scope need to exclude "hld,0,ded".
Here is the code
Sub createPivotTable()
Dim pt As PivotTable
Dim wrkSht As Worksheet
Dim pvtSht As Worksheet
Dim PTCache As PivotCache
Dim PRange As Range
Dim finalRow As Long
Dim finalCol As Long
Dim pvtItm As PivotItem
Dim visPvtItm As String
Dim pvtFld As PivotField
Dim grRng As Range
Set wrkSht = Worksheets("Store Schedule Information")
Set pvtSht = Worksheets("All Brands - SD Due Date (2)")
finalRow = wrkSht.Cells(Application.Rows.Count, 1).End(xlUp).Row
finalCol = wrkSht.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = wrkSht.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set pt = PTCache.createPivotTable(TableDestination:=pvtSht.Cells(1, 1), TableName:="SDPivot")
pt.ManualUpdate = True
Set pvtFld = pt.PivotFields("Status")
pvtFld.Orientation = xlPageField
Set pvtFld = pt.PivotFields("Scope")
pvtFld.Orientation = xlPageField
Set pvtFld = pt.PivotFields("Brand")
pvtFld.Orientation = xlColumnField
Set pvtFld = pt.PivotFields("SD Complete Forecast")
pvtFld.Orientation = xlRowField
With pt.PivotFields("Designer")
.Orientation = xlDataField
.Function = xlCount
.NumberFormat = "#,##0"
.Position = 1
End With
Set grRng = pt.PivotFields("SD Complete Forecast").DataRange
grRng.Cells(1).Group Periods:=Array(False, False, False, False, True, False, True)
'to ungroup:
'grRng.Cells(1).Ungroup
pt.ManualUpdate = False
End Sub
ASKER
Line 61 is where I am receiving the error, I realized that the .DataRange was not identified, but even after I updated to .PRange still received the same error message.
Attaching a sample copy of the workbook.
EE-WAR-Sample.xlsm
Attaching a sample copy of the workbook.
EE-WAR-Sample.xlsm
You have three rows that have #N/A for the date: 159, 1192 & 1230. These make it impossible to group by month & year. Once you delete those rows (and readjust the PivotTable source), then it is possible to do your grouping.
ASKER
What do you mean by readjust the PivotTable source? Removed the three lines and get new error 438:Okbject doesn't support this property or method.
I'm still working on the macro.
Readjust the PivotTable source means to click the PivotTable Tools...Options...Change Data Source menu item when you are manually manipulating your PivotTable. The macro is doing this part for you, but I wanted to record the bit with the grouping using your sample PivotTable.
Readjust the PivotTable source means to click the PivotTable Tools...Options...Change Data Source menu item when you are manually manipulating your PivotTable. The macro is doing this part for you, but I wanted to record the bit with the grouping using your sample PivotTable.
The following code is working:
Sub createPivotTable()
Dim pt As PivotTable
Dim wrkSht As Worksheet
Dim pvtSht As Worksheet
Dim PTCache As PivotCache
Dim PRange As Range
Dim finalRow As Long
Dim finalCol As Long
Dim pvtItm As PivotItem
Dim visPvtItm As String
Dim pvtFld As PivotField
Dim grRng As Range
Set wrkSht = Worksheets("Store Schedule Information")
Set pvtSht = Worksheets("All Brands - SD Due Date (2)")
finalRow = wrkSht.Cells(Application.Rows.Count, 1).End(xlUp).Row
finalCol = wrkSht.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = wrkSht.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
'Refresh all Pivot Tables in a worksheet:
'For Each pt In Worksheets("All Brands - SD Due Date (2)").PivotTables
' pt.RefreshTable
' Next
'Create Pivot Table
Set pt = PTCache.createPivotTable(TableDestination:=pvtSht.Cells(1, 1), TableName:="SDPivot")
pt.ManualUpdate = True
Set pvtFld = pt.PivotFields("Status")
pvtFld.Orientation = xlPageField
Set pvtFld = pt.PivotFields("Scope")
pvtFld.Orientation = xlPageField
Set pvtFld = pt.PivotFields("Brand")
pvtFld.Orientation = xlColumnField
Set pvtFld = pt.PivotFields("SD Complete Forecast")
pvtFld.Orientation = xlRowField
With pt.PivotFields("Designer")
.Orientation = xlDataField
.Function = xlCount
.NumberFormat = "#,##0"
.Position = 1
End With
pt.PivotFields("Brand").PivotItems("XXH").Visible = False
pt.PivotFields("Brand").PivotItems("XXS").Visible = False
pt.PivotFields("Brand").PivotItems("XXSB").Visible = False
pt.PivotFields("Brand").PivotItems("XXW").Visible = False
'pt.PivotFields("Brand").PivotItems("#N/A").Visible = False
For Each pvtItm In pt.PivotFields("Brand").PivotItems
If pvtItm.Visible = True Then
visPvtItm = visPvtItm & ", " & pvtItm
End If
Next
pt.PivotCache.Refresh
pt.ManualUpdate = False
Application.OnTime Now(), "FinishPT"
End Sub
Private Sub FinishPT()
Dim grRng As Range
With Worksheets("All Brands - SD Due Date (2)").PivotTables("SDPivot")
Set grRng = .PivotFields("SD Complete Forecast").DataRange
grRng.Cells(1).Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
'to ungroup:
'grRng.Cells(1).Ungroup
End With
End Sub
EE-WAR-SampleQ28361274.xlsm
ASKER
I got that to work, and took your suggestion on recording a macro so I firgured out how to filter the data for the second question, however is can you tell me if there is a cleaner way to exclude data from the grouping? I only need see anything that is 2012 and greater.
When I recorded I got the following:
When I recorded I got the following:
With ActiveSheet.PivotTables("SDPivot").PivotFields("Years")
.PivotItems("<1/0/1900").Visible = False
.PivotItems("1900").Visible = False
.PivotItems("1901").Visible = False
.PivotItems("1902").Visible = False
.PivotItems("1903").Visible = False
.PivotItems("1904").Visible = False
.PivotItems("1905").Visible = False
.PivotItems("1906").Visible = False
.PivotItems("1907").Visible = False
.PivotItems("1908").Visible = False
.PivotItems("1909").Visible = False
.PivotItems("1910").Visible = False
.PivotItems("1911").Visible = False
.PivotItems("1912").Visible = False
.PivotItems("1913").Visible = False
.PivotItems("1914").Visible = False
.PivotItems("1915").Visible = False
.PivotItems("1916").Visible = False
.PivotItems("1917").Visible = False
.PivotItems("1918").Visible = False
.PivotItems("1919").Visible = False
.PivotItems("1920").Visible = False
.PivotItems("1921").Visible = False
.PivotItems("1922").Visible = False
.PivotItems("1923").Visible = False
.PivotItems("1924").Visible = False
.PivotItems("1925").Visible = False
.PivotItems("1926").Visible = False
.PivotItems("1927").Visible = False
.PivotItems("1928").Visible = False
.PivotItems("1929").Visible = False
.PivotItems("1930").Visible = False
.PivotItems("1931").Visible = False
.PivotItems("1932").Visible = False
.PivotItems("1933").Visible = False
.PivotItems("1934").Visible = False
.PivotItems("1935").Visible = False
.PivotItems("1936").Visible = False
.PivotItems("1937").Visible = False
.PivotItems("1938").Visible = False
.PivotItems("1939").Visible = False
.PivotItems("1940").Visible = False
.PivotItems("1941").Visible = False
.PivotItems("1942").Visible = False
.PivotItems("1943").Visible = False
.PivotItems("1944").Visible = False
.PivotItems("1945").Visible = False
.PivotItems("1946").Visible = False
.PivotItems("1947").Visible = False
.PivotItems("1948").Visible = False
End With
With ActiveSheet.PivotTables("SDPivot").PivotFields("Years")
.PivotItems("1949").Visible = False
.PivotItems("1950").Visible = False
.PivotItems("1951").Visible = False
.PivotItems("1952").Visible = False
.PivotItems("1953").Visible = False
.PivotItems("1954").Visible = False
.PivotItems("1955").Visible = False
.PivotItems("1956").Visible = False
.PivotItems("1957").Visible = False
.PivotItems("1958").Visible = False
.PivotItems("1959").Visible = False
.PivotItems("1960").Visible = False
.PivotItems("1961").Visible = False
.PivotItems("1962").Visible = False
.PivotItems("1963").Visible = False
.PivotItems("1964").Visible = False
.PivotItems("1965").Visible = False
.PivotItems("1966").Visible = False
.PivotItems("1967").Visible = False
.PivotItems("1968").Visible = False
.PivotItems("1969").Visible = False
.PivotItems("1970").Visible = False
.PivotItems("1971").Visible = False
.PivotItems("1972").Visible = False
.PivotItems("1973").Visible = False
.PivotItems("1974").Visible = False
.PivotItems("1975").Visible = False
.PivotItems("1976").Visible = False
.PivotItems("1977").Visible = False
.PivotItems("1978").Visible = False
.PivotItems("1979").Visible = False
.PivotItems("1980").Visible = False
.PivotItems("1981").Visible = False
.PivotItems("1982").Visible = False
.PivotItems("1983").Visible = False
.PivotItems("1984").Visible = False
.PivotItems("1985").Visible = False
.PivotItems("1986").Visible = False
.PivotItems("1987").Visible = False
.PivotItems("1988").Visible = False
.PivotItems("1989").Visible = False
.PivotItems("1990").Visible = False
.PivotItems("1991").Visible = False
.PivotItems("1992").Visible = False
.PivotItems("1993").Visible = False
.PivotItems("1994").Visible = False
.PivotItems("1995").Visible = False
.PivotItems("1996").Visible = False
.PivotItems("1997").Visible = False
.PivotItems("1998").Visible = False
.PivotItems("1999").Visible = False
End With
With ActiveSheet.PivotTables("SDPivot").PivotFields("Years")
.PivotItems("2000").Visible = False
.PivotItems("2001").Visible = False
.PivotItems("2002").Visible = False
.PivotItems("2003").Visible = False
.PivotItems("2004").Visible = False
.PivotItems("2005").Visible = False
.PivotItems("2006").Visible = False
.PivotItems("2007").Visible = False
.PivotItems("2008").Visible = False
.PivotItems("2009").Visible = False
.PivotItems("2010").Visible = False
.PivotItems("2011").Visible = False
.PivotItems("2016").Visible = False
.PivotItems(">3/5/2016").Visible = False
End With
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
FWIW, I don't write PivotTable macros from scratch, but rather record them and then edit extensively. I do a lot of testing during the editing process to make sure I am not creating a problem.
When trying to debug macros, it is helpful to have a sample workbook. That's especially true with 1004 errors. Could you please post a workbook that demonstrates the problem?
PivotTable macros will vary with the version of Excel that you are using. Could you please specify?