Automating Reports in Excel

Automating Reports in Excel
Featured

Automating Reports in Excel

Download Brochure Register Check Scheduled Training

 

Overview

Have you felt bore in preparing same reports over and over again? Have you ever imagine that Excel can automate generate reports for you? Do you fear about the complexity of VBA programming?

If your answers are nothing but “YES”, this course is designed especially for you. You’ll learn how to record dynamic Macro, make changes to it. So that you can automate your reports with a single mouse click.

This training course is designed to equip you with core skills in reading the VBA code. Subsequently, you will be exposed to essential Macro recording planning and techniques. With these techniques and skills, you can automate your reports right after this course.

Who Must Attend
Regular Excel users that want to automate their reports with moderate level of VBA (Visual Basic for Applications) programming.

OUTCOME

At the end of this course you will be able to:

  • Aware the significance of I.P.O. (input-process-output) in reporting
  • Know the fundamental strategies in dynamic Macro recording
  • Plan and record Macro with PYOM Sheet
  • Perform adjustments on recorded Macro
  • Aware the common mistakes in automated reporting
  • Add branching & repetition logic to automate reporting
  • Manage and share Macro

PREREQUISITES

To ensure your success, we recommend the delegates equip with the following or equivalent knowledge:

  • An advanced working knowledge of Microsoft Excel
  • A working knowledge of Microsoft Windows
  • A working knowledge of computer, keyboard and mouse
  • A basic understanding on programming is a plus

DURATION

3 Days (9 am - 5 pm)

METHODOLOGY

Face-to-face learning with demonstrations and practical hands-on.

Training Outline

 

Day 1

1. Introduction to Macro/ VBA

  • How to Create Excel Macro/ VBA
  • What is Macro Recorder
  • Limitations of Macro Recorder
  • What is VBA
  • Macro Recording vs. VBA

 

2. Setting the Stage

  • Showing Developer Ribbon
  • Macro Security
  • Save File as Macro-Enabled Workbook
  • Saving Macro-Enabled Workbook

 

3. Visual Basic for Application (VBA)

  • Working in Basic Editor (VBE)
  • General Structure (Syntax) of VBA
  • Adding Comment
  • Creating Sub Procedure

 

4. Simple Interactions with User

  • Message Boxes
  • Input Boxes

 

5. Running the Macro

  • via Macro Menu
  • via Shortcut Key
  • via Quick Access Toolbar
  • via Shape/ Image
  • via Button

 

6. Running the Macro in Slow Motion (Debugging)

  • Stepping Through
  • Breakpoint
  • Immediate Window

 

7. Dynamic Macro Recording

  • Benefits of Macro Recording
  • Observe How Macro Recording Generates VBA Code
  • Typical Mistakes in Dynamic Macro Recording
  • Essential Techniques of Dynamic Macro Recording
  • Using PYOM™ (Plan Your Own Macro) Sheet
  • Recording Macro
  • Viewing Macro
  • Editing Macro
  • Deleting Macro

 

8. The Basics of Automated Reporting – Cells/ Range

  • Planning Action of Cells/ Range with PYOM Sheet
  • Edit Recorded Macro to Make it Dynamic to Fit Many Situations
  • Outputting a Specific Text to a Selected Range of Cells
  • Affecting Multiple Cells with One RANGE Command
  • Using a Named Range with the RANGE Object
  • Experimenting with RANGE Properties (VALUE, TEXT, ROW, COLUMN, SELECT, COUNT, ADDRESS, FORMULA, NUMBERFORMAT, FONT BOND, UNDERLINE, ITALICS)
  • Using CELLS Object Instead of RANGE Object
  • Using Column Letters instead of Numbers in CELLS Object
  • Using CELLS position within a Range
  • Affecting All Cells in a Worksheet
  • Using RANGE Object with CELLS Object

 

9. The Basics of Automated Reporting - Worksheet

  • Planning Action of Worksheet with PYOM Sheet
  • Edit Recorded Macro to Make it Dynamic to Fit Many Situations
  • Referring to a Range of Current Worksheet
  • Referring to a Range of Specific Worksheet
  • Looping All Worksheets
  • Creating a New Worksheet
  • Checking for Existence of a Worksheet
  • Deleting a Worksheet
  • Disabling Warning Prior Deleting a Worksheet

 

10. The Basics of Automated Reporting - Workbook

  • Planning Action of Workbook on PYOM Sheet
  • Edit Recorded Macro to Make it Dynamic to Fit Many Situations
  • Referring to Current Workbook
  • Checking for Existence of a Workbook
  • Create a New Workbook
  • Saving a Workbook
  • Closing a Workbook
  • Closing a Workbook Without Saving
  • Disabling Reminder when Closing an Unsaved Workbook

 

 

Day 2

11. Automated Import

  • Using PYOM Sheet in Automate Data Import
  • Edit Recorded Macro to Make it Dynamic to Fit Many Situations
  • The Benefits of Using Excel Table
  • Importing Data from a Specific Worksheet in Current Workbook
  • Prompting User to Select a File to Import
  • Importing Data from a Specific Worksheet in Another Workbook
  • Importing Data from All Worksheets in Another Workbook
  • Prompting User to Select a Folder to Import All Files in it
  • Importing Data from a Specific Worksheet in All Workbooks Found in a Specific Folder
  • Clearing All Previous Data Prior Importing New Data
  • Appending New Data into Existing Data

 

12. Automated Processing

  • Using PYOM Sheet in Automate Data Processing
  • Edit Recorded Macro to Make it Dynamic to Fit Many Situations
  • Inserting Columns with New Formulas Filled Down
  • Deleting Specific Columns or Rows
  • Converting Formulas to Values to Avoid Errors
  • Looping Through Data in a Column with the FOR Loop
  • Using CELLS To Access a Cell
  • Looping Through Variable Length Lists
  • Looping Through Data in a Columns with Spaces
  • Using Comparison Operators (=, <, >, <=, >=, <>)
  • Calling Formula to Determine When to Stop a Loop
  • Using IF Criterion to Determine Alternate Processing Logic
  • Using Multiple Criteria with Nested IFs
  • Using SELECT CASE as an Alternative to IFs
  • Leveraging Functions Available in Excel (e.g.: VLOOKUP)

 

13. Automated Output

  • Using PYOM Sheet in Automate Report Output
  • Creating a New Pivot Table
  • Refresh Data for Existing Pivot Tables
  • Using PrintPreview Automatically
  • Using PrintOut to Send Directly to Default Printer
  • Output to PDF File

 

Day 3

14. Error Handling

  • Breakaway from Infinite Loop (i.e. Excel not responding after running a Macro)
  • Error Trapping the Easy Way
  • Ignore All Errors
  • Interact with the User When An Error Occurs
  • Making use of the ERR Object

 

15. Speeding Tips

  • Turning off Screen Updating
  • Preventing Alert Messages
  • Pause Automatic Calculation

 

16. Putting All Together

  • Importing Newer Data for Existing Reports
  • Updating Existing Report after Importing and Processing New Data
  • Automatically Create a Brand New Report from Scratch

 

17. Manage and Sharing Macro

  • Transfer Macro Between Excel Files
  • Transfer Macro Between Computers
  • Protect Macro with Password

 

Hardware & Software
  • A laptop is required for this course (PCs not provided)
  • Excel 2007 or later is installed

Interested to Register?

Let's have one of our friendly consultants to guide you through the registration process.

Looking for Something Unique or In-house Training?

Let's have one of our friendly consultants talk to you...

Skip to toolbar