Clean Data with ChatGPT: Create and Refresh Pivot Table with VBA
00:22 Business Scenario
01:02 Write Code in ChatGPT
02:46 Second Trial
03:57 Refresh with New Data
VBA Code used in this video:
_____________________________________________________
Sub CreatePivotTable()
Dim ws As Worksheet
Dim ptCache As PivotCache
Dim pt As PivotTable
Dim dataRange As Range
Dim ptSheet As Worksheet
Dim ptRange As Range
' Set the reference to the "employee_records" sheet
Set ws = ThisWorkbook.Sheets("employee_records")
' Define the data range for the pivot table (adjust the range as per your data)
Set dataRange = ws.Range("A1").CurrentRegion
' Create a new worksheet for the pivot table
Set ptSheet = ThisWorkbook.Sheets.Add(After:=ws)
ptSheet.Name = "PivotTableSheet" ' Change the name of the sheet if desired
' Set the range where you want the pivot table to be placed on the new sheet
Set ptRange = ptSheet.Cells(1, 1) ' You can change the row and column numbers here
' Create a new pivot cache
Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataRange)
' Add a new pivot table to the new sheet
Set pt = ptCache.CreatePivotTable(TableDestination:=ptRange, TableName:="PivotTable1")
' Add fields to the pivot table
With pt
' Add the "Country" field to the Rows area
.PivotFields("Country").Orientation = xlRowField
' Add the "Department" field to the Rows area
.PivotFields("Department").Orientation = xlRowField
' Add the "Employee ID" field to the Values area and set it to show the count of employees
.AddDataField .PivotFields("employee_id"), "Headcount", xlCount
End With
End Sub
_____________________________________________________
Related Links:
https://openai.com/blog/chatgpt
Want to learn how to design a salary structure? Check: https://bit.ly/3r3KXeW
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
*****More Videos in Playlists*****
Coding with ChatGPT https://bit.ly/3YtsmWe
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 vs Power BI: https://bit.ly/3NQWN51
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 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
#ExcelforHR#HRAnalytics#Excel#HR
Смотрите видео Clean Data with ChatGPT: Create and Refresh Pivot Table with VBA онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Caripros HR Analytics 15 Ноябрь 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 350 раз и оно понравилось 7 людям.