Excel Macros & VBA for Excel Report Automation

Name: Excel Macros & VBA for Excel Report Automation
Rating: 4.6 (166 reviews)

Learn Macros Step by Step till Report Automation (End to End)

Created byRamzan Rajani

Last updated 3/2022

English

English [Auto],

What you'll learn

1) Macros Recording & Running and will start with Basic Codings
2) You will learn how to Consolidate Multiple Sheets into one
3) Filtering Project : Where you will learn to create Seperate Sheet for each Filtered items with Simple Coding
4) You will also learn how to Implement IF Condition with Macros (VBA)
5) Consolidation Project : You will learn how to write the Code to Consolidate Multiple Sheets of Multiple Files

Course content

5 sections • 33 lectures • 5h 35m total length

Recording and Running Macro11:36
Learn to record and run Excel macros to automate data formatting, start recording, perform actions, stop recording, then reuse the macro on new data via a shortcut, saved as .xlsm.
Download Practice File
Recording Macro Practice11:58
Record a macro in Excel using keyboard shortcuts like Ctrl+Shift+Right and Ctrl+Shift+B, and compare with mouse-driven recording. Save as a macro-enabled workbook (.xlsm) and run on multiple sheets.
Creating Button to Run the Macro12:57
Learn to create and assign buttons to run macros in Excel, using form controls and the quick access toolbar for a macro across multiple sheets.
Macro Query Solution on Relative Reference by Participant6:01
Learn to use relative references when recording Excel macros, so the macro runs from the cursor position, not first row. The lesson shows enabling this option and recording formatting steps.
Starting Basic Editing in Macro15:03
Learn to edit a recorded Excel macro with the VBA editor, Project Explorer, and modules, and adjust heading formatting for reports, while noting macro security basics.
Commenting Code and adding Module12:22
Learn how to comment code in VBA, add and manage modules, create and run macros, use message boxes, and apply best practices for Excel automation.
Creating Copy Paste Macro19:45
Explore creating a copy-paste macro in VBA to copy data from sheet 1 and paste into a new sheet, with practical tips on macro recording and editing.
Deleting data before pasting Macro9:25
Explore how to use an Excel macro to delete existing data before pasting new data, by clearing contents or entire columns, and ensure smooth cross-sheet pasting in report automation.
Adding & Renaming Sheet Macro Code6:17
Learn to build a VBA macro that inserts a new worksheet, renames it dynamically, copies data from a range, and pastes it for end-to-end report automation.
Debugging Macro6:08
Learn to debug Excel macros by stepping through code with F8, observe each line's effect on data and sheets, and verify how the macro executes step by step.
Breakpoints in Macro5:14
Learn to set and manage breakpoints in Excel macros, then run to them or step through code with F8 to debug VBA and inspect recent records.
Creating New File and Pasting Data5:36
Learn to automate Excel data transfer by creating a new file, copying a sheet's data, renaming the sheet to data, and pasting into the new workbook via a macro.
Thisworkbook & Activate Concept17:20
Learn to manage macro workbooks in Excel by saving macro files, opening and activating workbooks, and transferring data between files with copy and paste.
Filtering Macro Code (Use for Report Automation)16:36
Learn to automate report creation in Excel using VBA macros. Build a filtering tool that creates per-location sheets from a master data set, using recording, copy-paste, and looping.
For Looping Project17:41
Learn how to automate Excel reporting with VBA loops, creating dynamic sheets per city, using a control sheet for criteria, and looping a macro to generate filtered reports.
Revision of all above 15 Videos in just 17 Mins17:31
Conduct a rapid revision of Excel macros and VBA basics, covering recording and running macros, creating buttons, enabling developer, editing code, and basic cross-workbook automation.

Project Filter Part 115:18
Build a dynamic VBA macro to filter master data by city and copy each location's records to its own sheet. Drive the process with a control sheet listing city names.
Project Filter Part 29:57
Learn to automate Excel report filtering with a dynamic for loop in VBA, pulling criteria from a control sheet, copying results, and creating a separate sheet for each city.
Using Formula in Excel VBA7:01
Learn how to write and execute Excel formulas in VBA by embedding them with Application.WorksheetFunction, using count, managing ranges, and triggering via a button for automated reports.
Pulling name from Seperate Sheet (Making Dynamic)1:25
Explains making dynamic data pulls in Excel macros by explicitly specifying the source sheet while counting a range, avoiding the active sheet assumption.
Creating Seperate Sheet in New File for each City15:06
Learn how to use Excel VBA macros to create a separate workbook for each city, generate and name sheets, copy and filter data, and automate saving.
Creating Separate File for each city15:37
Create a macro that generates a separate file for each city, saves it with a city-based name from a control sheet, and stores files in a dedicated folder.

Using IF Condition7:23
Learn how to use if condition in VBA to control when a macro runs, check header names, handle blank cells, and update data in Excel reports.
Participant Query in IF Condition for Deleting Column1:25
Explore using an if condition to delete an entire column in Excel when the heading is blank, selecting range B:D and applying delete via macro.
Using ElseIF in Macro VBA3:12
Explore else if in macro VBA to handle name and blank conditions, assigning the participant name or a default participant value with multiple branches.

Creating Consolidation Macro Part 1 (Step by Step)11:54
learn to consolidate multiple city sheets into a single master sheet with a step-by-step macro, creating a consolidation file and pasting data from each sheet.
Creating Consolidation Macro Part 28:24
Learn to automate data consolidation in Excel by copying new data below the last row using VBA, recording macros, and calculating the last row to paste seamlessly.
Creating Consolidation Macro Part 39:54
Create a dynamic consolidation macro that counts all sheets, uses a for loop from 1 to total sheets, and copies data into the consolidation sheet.
Macro Consolidation from Multiple Files Part 1 (Step by Step)14:06
Automate Excel report consolidation across multiple files using macros and VBA, opening files from a folder, copying headings, and pasting into a consolidation sheet guided by a control sheet.
Macro Consolidation from Multiple Files Part 27:32
Consolidate data from multiple files with a macro: copy from the Dubai file, paste into the main workbook, and manage the last row and file activation.
Macro Consolidation from Multiple Files Part 36:30
Consolidate data from multiple workbooks by opening files listed in a control sheet, copying into a consolidation sheet, looping through files, and running in the background with screen updating off.

Requirements

MS Excel 2010 & above version
Basic Knowledge of Excel Formulas

Description

Excel Macros - VBA for Report Automation
The objective of this course is to make you expert in writing own macros code Exclusive for Report Automation.

If you see the course outline its all practical topics and only those codes has been taught which is regularly used in the report automation.

The other benefit is its a complete class room training hence i have very slow step by step to make you understand each topics.

In this course i have even covered Sheets and Files Consolidation & Segregation Project which is regular required in Report Automation in Corporates.

Overview

Advanced Excel users encounter repetitive tasks such as: reports that need to be produced, data that needs to be updated or tables that need to be formatted on a regular basis. Using “Macros” many of these routine tasks can be automated. Using advanced knowledge of Excel learned in this class they can record the steps or operations that they want to perform, and then press a button to repeat them over and over again (and again).

Prerequisite : Proficient knowledge of Microsoft Excel. This class is for non-programmers.

Target Student

This course is for individuals who use Excel 2016, 2013 or 2010 frequently and who need to automate routine tasks. Programming experience is not required.

Thanks & Regards

Ramzan Rajani

Microsoft Excel Specialist and Certified Trainer

Who this course is for:

Those interested in learning Excel Macros to Automate Regular reports
Finance Professionals
Marketing Executives
Admin
HR Professionals
Project Co-ordinators
Account Professionals

Excel Macros & VBA for Excel Report Automation

What you'll learn

Explore related topics

Course content

Macro Recording, Running & Starting with Codings17 lectures • 3hr 12min

Project6 lectures • 1hr 4min

IF Condition3 lectures • 12min

Consolidation Macro Project6 lectures • 58min

Extra Projects (If you wish to Learn and get more ideas)1 lecture • 9min

Requirements

Description

Who this course is for: