Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am trying to write macros that create two pivot tables from separate sheets back to back, and group them by month but every time I try to run my code I keep getting an error. Here is my code:

    Sub PivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Sheets("Information").UsedRange).CreatePivotTable TableDestination:="Pivot!R1C4", TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion10
With Sheets("Pivot").PivotTables("PivotTable").PivotFields("PN")
  .Orientation = xlRowField
 .Position = 1
End With
         With Sheets("Pivot").PivotTables("PivotTable").PivotFields("Commit")
 .Orientation = xlColumnField
 .Position = 1
         End With
Sheets("Pivot").PivotTables("PivotTable").AddDataField Sheets("Pivot").PivotTables("PivotTable").PivotFields("Qty"), "Sum", xlSum
End Sub
     Sub GroupPivot()
   Dim therange As Range
   Dim PT As PivotTable
   Set PT = Sheets("Pivot").PivotTables("PivotTable")
   Set therange = PT.PivotFields("Commit").DataRange.Cells(1)
   therange.Select
   Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
End Sub
Sub PivotTableNY()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Sheets("InformationNextYear").UsedRange).CreatePivotTable TableDestination:="PivotNextYear!R1C4", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
With Sheets("PivotNextYear").PivotTables("PivotTable1").PivotFields("Material")
  .Orientation = xlRowField
 .Position = 1
End With
  With Sheets("PivotNextYear").PivotTables("PivotTable1").PivotFields("Deliv. Date")
 .Orientation = xlColumnField
 .Position = 1
 End With
Sheets("PivotNextYear").PivotTables("PivotTable1").AddDataField Sheets("PivotNextYear").PivotTables("PivotTable1").PivotFields("Open Quantity"), "Sum", xlSum
End Sub
Sub GroupPivotNY()
Dim myrange As Range
Dim PT As PivotTable
Set PT = Sheets("PivotNextYear").PivotTables("PivotTable1")
Set myrange = PT.PivotFields("Material").DataRange.Cells(1)
myrange.Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
End Sub
I keep getting this error message:
  
"Rum time Error '1004': Select Method of Range class failed."
The error appears in line: therange.Select under Sub GroupPivot.
Can someone help me resolve this error?
                also just write theRange.Group ... if you try to select a range and a different sheet is active then what you expect it will fail.
– Scott Holtzman
                Aug 3, 2017 at 15:35
                @dwirony what two numbers do I put in there? That happens to be some code I found online and I don't understand that bit too well. My pivot table starts D1, and the dates I want to group start on E2
– cthallofamer
                Aug 3, 2017 at 15:41
                @ScottHoltzman I got rid of the select and it gave me the error: Cannot group that selection. Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _ False, True, False, False) End Sub was where I got that error. Is this because of what the other comment was saying? I need to put 2 numbers in criteria?
– cthallofamer
                Aug 3, 2017 at 15:43
                @ctallofamer It depends on what you're trying to do, which I cannot tell... Could you try and clean up this bit of code? it's 6 subroutines with 0 formatting, it's an eyesore to try and navigate.
– dwirony
                Aug 3, 2017 at 15:44
                What's the purpose of Cells(1)? Without being able to test it myself, I'd try removing .Cells(1) so it reads: Set therange = PT.PivotFields("Commit").DataRange
– K Paul
                Aug 3, 2017 at 16:01
                Okay, let's back this up. Put .Cells(1) back in. It's necessary for the grouping function I think. Then go back through your data and make sure you don't have any blanks in the date column that you're trying to group. Let me know if that works.
– K Paul
                Aug 3, 2017 at 16:59
                What error is it giving you for the 2nd pivot table? Did you use the correct sheet name in the Worksheets("").Activate statement?
– K Paul
                Aug 3, 2017 at 17:11
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.