Please enable JavaScript.
Coggle requires JavaScript to display documents.
Learn to Automate Reports with Excel Macro in one day (Learning steps…
Learn to
Automate Reports
with Excel Macro
in one day
Requirement
You should know how to create report manually with Excel's functions and features such as formula, Data filter, Pivot table etc.
Programming or Excel Macro background are not required
One free day to follow the instruction and made understanding
Objectives
Learn how to automate report creation with Excel Macro in one day
Learn only what is needed in well organized steps.
Programming or Excel Macro background are not required
Topics to learn
How to record Macro
Add Developer tab
How to save Excel file with Macro
How to edit Macro
How to find last row number
How to combine multiple small macro and run in order
How to run the created macro
Over View of Visual Basic interface
Insert Pivot table and modify code
Learning steps
General Discussion
Objectives
Requirement
Result after learning
What do we will learn
Sample Data introduction
Import from other source such as
ERP, POS, Accounting
Made duplicate for multiple trials in Data1 to Data3
Column and Record
Add Developer tab
What item we will use
Record Macro introduction
Record Dialog box overview
Sample of the other Macro video for further study
But not a requirement
Record data filter
Filter ship mode not Air
Filter ship mode not high priority
Objective of report to see how effective of arrange shipping by select only the shipping mode by air with is not high priority and in large size by filter the other and deleted row
Filter the packaging with not large size
Delete rows
Show the last rows before process
Show the last row after delete
Rearrange windows to see real time code generate
Open Visual Basic Windows
Walk through the Visual Basic Windows
Show how to save Excel file with Macro
How to run the recorded Macro on the Data2
And why cannot run on Data1 again
Record of Pivot Table in the same sheet
Run Recorded Macro and show how it is error
Show how to find last row and change code
Start from beginning concept
Sheet Select and Cell Select
Conclusion
Start with same status which you record macro such as number of Sheet, Sheet name, number of column and column arrangement except number of row
Change Rows Number in Code if you make change in Row number such as delete or insert
Make the code modification when insert Pivot table
Rename Sheet the you just create if you want to refer it again such as delete it.
Record in many small session then test and combine into one main sub
Create user interface for run the Macro such as Button
Save your Excel file that include Macro with "xlsm" extension
Locate the Starting Point such as Start Sheet and Start Cell in the record