How to Create Pivot Table for All Worksheets with 1 Click - Part 2
Want to learn how to design a salary structure? Check: https://www.caripros.com/design-salar...
FREE template for my video: Excel for HR - Create Annual Employee Salary Increase Template from Scratch. You can download and try it out yourself here: https://bit.ly/2MLLdb7
FREE actual workbook for my video "Split a Master Spreadsheet into Multiple Sheets with 1 click - VBA for Beginner". You can download and try it out yourself here: https://bit.ly/2UmeX2v
New course Launched! I created it to show you step-by-step how to design a salary structure with regression analysis in Excel. Check out the detail here:
https://caripros-hr-analytics.teachab... Topic: How to create pivot table for all worksheets with 1 click
Scenario: You want to auto select your data and create pivot table with macro
Function: Macro
Related Video:
How to Create Pivot Table for All Worksheets with 1 Click - Part 1
• How to Create Pivot Table for All Workshee...
Split a Master Spreadsheet into Multiple Sheets with 1 click - VBA for Beginner
• Split a Master Spreadsheet into Multiple S...
Troubleshooting when your code does not work: • Why my Sheet-Splitting Macro Code does not...
**Macro Code SEE COMMENT FOR IMPORTANT NOTICE**
Sub SplitandFilterSheetandCreatePivotTable()
'Step 1 - Name your ranges and Copy sheet
'Step 2 - Filter by Department and delete rows not applicable
'Step 3 - Loop until the end of the list
Dim Splitcode As Range
Sheets("Master").Select
Set Splitcode = Range("Splitcode")
For Each cell In Splitcode
Sheets("Master").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = cell.Value
With ActiveWorkbook.Sheets(cell.Value).Range("MasterData")
.AutoFilter Field:=6, Criteria1:="NOT EQUAL TO" & cell.Value, Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilter.ShowAllData
'add in the creating pivot table code set
On Error Resume Next
'select your dataset range for pivot table
Range("MasterData").Select
'create pivot table
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Range("MasterData")).CreatePivotTable _
TableDestination:=Range("L5"), TableName:="PivotTable1"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Performance Rating")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("EE ID")
.Orientation = xlDataField
.Position = 1
.Function = xlCount
.Name = "Count of EE ID"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Base Salary")
.Orientation = xlDataField
.Position = 2
.Function = xlAverage
.Name = "Average of Base Salary"
.NumberFormat = "#,##0"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Country")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Country")
.PivotItems("Germany").Visible = False
.PivotItems("UK").Visible = False
.PivotItems("USA").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Country"). _
EnableMultiplePageItems = True
Next cell
End Sub
*****Follow-up Consulting Services*****
If you have specific question regarding your issue, you can email me at the email here https://goo.gl/WejijZ Note that there will be a fee of US$200 charged for solving your issue. The turnaround is within 24 hours. Any follow-up issue in 3 days will also be answered with no charge. Payment link: https://www.paypal.me/caripros
*****More Videos in Playlists*****
Power BI for Beginners: https://bit.ly/3ivKitD
Power BI for Advanced Users: http://bit.ly/3lE9zmO
Excel for HR https://goo.gl/JdeVnd
Excel for HR - Master Class https://goo.gl/LYfq2f
Excel Macro - Beginner https://goo.gl/Yae5nc
Excel Macro/VBA - Splitting a Master File https://goo.gl/m8CHya
Excel Macro/VBA - Auto-hide Rows or Columns http://bit.ly/2Mzteb5
Excel Charts Data Visualization https://goo.gl/2ao6BP
Excel Vlookup Function https://goo.gl/kP2Wpz
Excel Pivot Table Function https://goo.gl/rukkPs
Excel Array Function https://goo.gl/i4sQH8
Excel Index and Match Function https://goo.gl/i7VGU4
Excel Solver/Goal Seek Functions https://goo.gl/FTkTnj
Excel Cell Formatting Solutions https://goo.gl/gpa6MY
HR Analytics - Merit Matrix https://goo.gl/Koy7co
HR Analytics - Salary Structure https://goo.gl/uZBnFa
Excel Tricks https://goo.gl/TeqGDw
Excel Troubleshooting https://goo.gl/bdY5by
Fun HR Topics https://goo.gl/7zVg8h
For more successful stories, view at: http://caripros.com/index.php/success...
#ExcelforHR#HRAnalytics#Excel#HR