How to refresh a pivot table and at the same time perform normal filtering on a raw data with Macro?

[seanteo]

Hi all,

I am using macro to perform refreshing of 2 pivot tables currently. 1st pivot table is a very simple table with just a few fields on it. However when comes to the 2nd table, the data is enourmous and it is taking a very long time to open file, refresh table, etc.

My selection is from a drop down list in another sheet (Main, cell G16), 1st pivot table is named Summary, 2nd pivot table is named Detailed.

Is there any way to just not use pivot table in Detailed sheet ? How would the codes be ? Cell G16 contains client's name. If client name ALI is being selected, 1st pivot will show Ali's summary, while 2nd pivot will show Ali's detailed report. I want the 2nd pivot table to be changed to just a normal data filtered table based on client's name selected (could be the raw data itself from another sheet or same sheet).

Thanks in advance.

Sub SelectClientName()
  On Error GoTo Err
  Dim p As PivotTable
  Dim sht As Worksheet
  For Each sht In ThisWorkbook.Sheets
  For Each p In sht.PivotTables
  Select Case sht.Name
  Case "Client_S"
  Case Else
  If Range("Clientname") = "" Then
  p.PivotFields("Client").CurrentPage = _
  "(All)"
  Else
  
  clientname = Sheets("Client_S").Cells(4 + Range("Clientname"), 1).Value
  
  a = sht.Name
  p.PivotFields("Client").CurrentPage = _
  clientname
  End If
  p.RefreshTable
  
  End Select
  Next
  Next
  
  MsgBox "Pivot Updated !!!", vbInformation, "Completed"
  
  Exit Sub
Err:
  MsgBox "Client name not found"
  
End Sub

Sub RefreshPivot()
  Range("Clientname") = ""
  Application.CalculateFull
  Dim p As PivotTable
  Dim sht As Worksheet
  For Each sht In ThisWorkbook.Sheets
  For Each p In sht.PivotTables
  p.RefreshTable
  Next
  Next
End Sub
  
 

  
Sub CreateMenu()
  Dim NewMenu As CommandBarPopup
  Dim MenuItem
  Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, temporary:=True)
  
  NewMenu.Caption = "CSO Explorer"
  
  Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
  With MenuItem
  .Caption = "CSO Explorer"
  .FaceId = 162
  .OnAction = "CSO"
  End With
  
End Sub