jonastiger
Member
Hi
I have the code below to generate a report as a new workbook with 3 sheets:
Team performance and 2 pivots.
There are several teams named A, B, C, D,..., N, each one with its own sheet.
Pivot sheetshave data that can be filtered by team.
For each team I have a button click with the code below to generate the report, but I need to change pivot filters manually so the data matches the correct team, before I click to generate the report.
I'm asking if there is a way to add automation instruction to filter pivots with the correct team name (letter).
Thank you very much
JT
I have the code below to generate a report as a new workbook with 3 sheets:
Team performance and 2 pivots.
There are several teams named A, B, C, D,..., N, each one with its own sheet.
Pivot sheetshave data that can be filtered by team.
For each team I have a button click with the code below to generate the report, but I need to change pivot filters manually so the data matches the correct team, before I click to generate the report.
I'm asking if there is a way to add automation instruction to filter pivots with the correct team name (letter).
Code:
Sub ReportA_Click()'Naming "Report" & team letter
Dim FName As String
Dim Sh As Worksheet
Application.ScreenUpdating = False
FName = "TeamA_Report_" & Format(Now(), "dd-mmm-yyyy")
If Dir("C:\" & FName & ".xlsx") = vbNullString Then
Sheets(Array("Pivot01", "Pivot02","TeamA")).Copy
For Each Sh In ActiveWorkbook.Worksheets
With Cells
.Copy
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteColumnWidths
End With
Range("A1").Select
Next Sh
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FName & ".xlsx", xlNormal
End If
Application.CutCopyMode = False
Application.ScreenUpdating = False
End SubThank you very much
JT