0

I would like to macro grouping dates always so that there is a yearly range, i.e. if we have 2020M06 it would add it to Grupuj1.

enter image description here EDITED: enter image description here

wb.Sheets("analysis-tabele").Activate
wb.Sheets("analysis-tabele").Range("A4:A5").Select
    Selection.Group
    ActiveSheet.PivotTables("pivot5").PivotFields("pmf_VINT_M36"). _  '''<----------
I would like PivotFields 
          of pmf_VINT_M36 there was a code ticking off the penultimate option '''
wb.Sheets("analysis-tabele").Range("B4:N19").Copy Destination:=wb.Sheets("analysis-tabele").Range("B21")
Przemek Dabek
  • 519
  • 2
  • 14
  • Not a solution to your problem but you will benefit greatly from reading on [how to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1). – Raymond Wu Sep 27 '21 at 08:00
  • Use a `Select Case` statement of `Month(Date)` and provide all the code from `Case` 1 - 12? – Raymond Wu Sep 27 '21 at 08:00
  • Is there any chance to deselect always the penultimate option in this field ? how to use select case here ? where ? – Przemek Dabek Sep 27 '21 at 08:03
  • I'm not exactly familiar with PivotTable but based on limited testing, `Activesheet.PivotTables(1).PivotFields(Activesheet.PivotTables(1).PivotFields.Count - 2).Orientation = 0` will hide the 2nd last option. It's -2 from the count as there seems to be another field `Values` in addition to the PivotFields that you see on the task pane. @Whyexcelme – Raymond Wu Sep 27 '21 at 08:15
  • I edited my post and add image. I need deselected penultimate option always. And in your code `Activesheet.PivotTables(1).PivotFields(Activesheet.PivotTables(1).PivotFields.Count - 2).Orientation = 0' In PivotFields what i should put pmf_VINT ? – Przemek Dabek Sep 27 '21 at 08:26
  • Try `Activesheet.PivotTables(1).PivotFields(Activesheet.PivotTables(1).PivotFields.Count - 2).Orientation = 0` as it is, it's referring to the `PivotField` by index instead of name so you don't have to put `pmf_VINT` – Raymond Wu Sep 27 '21 at 08:31
  • Im edting once again, maybe it will be for you more clear :( thanks – Przemek Dabek Sep 27 '21 at 08:43
  • I'm not sure what's the purpose of your edited question, are you able to hide the 2nd last PivotField using the code in my comment above? @Whyexcelme – Raymond Wu Sep 27 '21 at 08:51
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/237549/discussion-between-whyexcelme-and-raymond-wu). – Przemek Dabek Sep 27 '21 at 08:52

0 Answers0