Consulting

Results 1 to 7 of 7

Thread: RE: Pivot filter Month selection - Automatic Population in respective column

  1. #1

    RE: Pivot filter Month selection - Automatic Population in respective column

    Dear all,

    I need an help using macro in pivot table filter. I have two pivot sheets, each should be refreshed and below auto population needs to be done using macro. Attached the excel data file for your reference.
    sheet -> "Item-Pencil " - Pivot 1- refresh the pivot select the Month to previous Month i.e. if we are 12th month select 11th month and if we are in feb 21, select Jan - 21.

    Column "A2 " value to be populating correct month

    sheet -> "Item-Pen set"
    Pivot 2- refresh the pivot select the Month to previous Month i.e. if we are 12th month select 11th month and if we are in feb 21, select Jan - 21.

    Column "B2 " value to be populating correct month

    Once respective columns updated, save the macro file and exist.

    Thanks for your help in advance.
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,616
    In the attached, the pivot tables on sheets Item-Pencil and Item-Pen Set will filter themselves to the previous month to the system date month, when that sheet is activated.
    You can alter any of the filters, but the OrderDate will always renew its filter on sheet activation.
    The data in columns A:H of those two sheets are ignored and left alone.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Hi p45Cal,

    Thanks for the code. But the actual help which I needed is something different. Please find the updated help needed:

    Once the pivot refresh individually the below thing should be done

    sheet -> "Item-Pencil " - Pivot 1- refresh the pivot select the Month to previous Month i.e. if we are 12th month select 11th month and if we are in feb 21, select Jan - 21.

    Column "L2 " value to be populating correct month


    Pivot 2- refresh the pivot select the period to last YTD periods i.e. if we are in 12th month select 1 to 11th month value and if we are in feb 21, select only jan 21 month

    Column "L2 " value to be populating correct month.

    The above mentioned month selection should be in number format like 1 to 11 instead of Jan -Dec.



    Thanks for your help in advance.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,616
    see attached
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Hi p45cal,

    Thanks for the updated code but still one thing is missed. Can you please check below and confirm?

    Also Please confirm whether refresh of those pivots individually been included in your code. If not i need to update the code for the same.

    sheet -> "Item-Pen Set

    Pivot 2- refresh the pivot and if we are in 12th month select 1 to 11th month value and if we are in feb 21, select only jan 21 month

    Column "L2 " value to be populating correct month.

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,616
    See attached.

    Quote Originally Posted by Keerthi@21 View Post
    Also Please confirm whether refresh of those pivots individually been included in your code. If not i need to update the code for the same.
    You should be able to work this out for yourself.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Hi p45al,


    Thank you so much for your code and patience. But, I could see the same logic in both pivot sheets.


    can you please help me to fix it by clicking on button and run those things individually, instead of selecting filter while activating the sheet.


    I have applied the same code in by using two buttons on each sheet in my original data, I am facing unable to get the pivotitems property of the pivotfield class error - Higlighted in Bold.


    Code for your reference:

    MTD Code:
    
    MTD - Pivot 1.jpg
    
    
    Sub Activate()
    
    
    
    
    
    
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Period")
    '.ClearAllFilters
      VisPIName = CStr(Month(DateAdd("m", -1, Date)))
      '.PivotItems(VisPIName).Visible = False
      .PivotItems(VisPIName).Visible = True
      Set yyy = .PivotItems
      For Each pvti In .PivotItems
        pvti.Visible = pvti.Name = VisPIName
      Next pvti
    End With
    
    
    Call UpdatePivotTableRange_1
    
    
    End Sub

    YTD Code:

    YTD-Pivot 2.jpg

    Sub Activate_Copy()
    
    
    'Call UpdatePivotTableRange_1
    
    
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Period").ClearAllFilters
    LastMonth = Month(DateAdd("m", -1, Date))
      VisPIName = CStr(LastMonth)
      .PivotItems(VisPIName).Visible = True
      Set yyy = .PivotItems
      On Error Resume Next
      For Each pvti In .PivotItems
        pvti.Visible = (pvti.Name = VisPIName) Or CLng(pvti.Name) <= LastMonth
      Next pvti
      On Error GoTo 0
    End With
    End Sub
    Please help out. Thanks for your help in advance.
    Last edited by Keerthi@21; 12-30-2020 at 10:08 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •