INTRODUCTION TO VBA & MACROS
Cells and Ranges
Copy And Paste
Controlling Fonts And Colouring Cells
Multiple Lines, Clearing And Column Widths
Building Logic With If
Building Logic With Select Case
The Importance Of Loops
Introduction To Automation
Loops And Selecting Ranges
Maneuvering And Macros
Constants And Variables
The Most Important Line In VBA You Need To Know!
Putting Together Everything So Far To Demonstrate Automation
AUTOMATING THE WORKSHEET
Macro File Formats
Developer Tab And Macro Security
Modules, Macros And Relative References
A First Look At Buttons
Charting Driven By Macros
Why Macros Can Go Wrong And What To Do About It
Handling Errors And Sheet Issues
Dynamic Charting: Part 1
Dynamic Charting: Part 2
Dynamic Charting: Part 3
End With
Controlling Autofilters
Controlling Formulas
GOING DEEPER INTO LOOPING
5 Levels Of Looping Part 1
5 Levels Of Looping Part 2
5 Levels Of Looping Part 3
5 Levels Of Looping Part 4
5 Levels Of Looping Part 5
VBA Alternative: Gathering Data With Power Query
AUTO-CREATE EXCEL FILES & TEMPLATES
Introduction
Creating An Input Sheet Template
Mass Producing Input Sheets 1: Set The Scene
Mass Producing Input Sheets 2: Worksheet Variables
Mass Producing Input Sheets 3: Autofilter Revisited And Paste special
Mass Producing Input Sheets 4: Manipulating Filter Criteria
Mass Producing Input Sheets 5 Creating A Unique List To Prepare For Looping
Mass Producing Input Sheets 6 Instantly Creating 25 Unique Input Excel Files
AUTOMATE DATA COLLATION & ANALYSIS
Starting The New Process
Open Up One File And Copy Data
Introduction To The Do While Loop
Looping Through All Files In A Folder And Copying Data
Setting Up New Data Before We Bring Into Our Long Boring List
Looking Up Latest Data Manually Into Our Long Boring List
Looking Up Latest Data Automatically Into Our Long Boring List
Putting It All Together
CONTROLLING POWERPOINT FROM EXCEL
Why PowerPoint?
Introduction to the Object Model
Programming in PowerPoint 1: Basics
Programming in PowerPoint 1: Shapes
Programming in PowerPoint 1: Tables
Opening PowerPoint from Excel
Pasting an Excel range as an Image in PowerPoint
Translating Excel data into a table in PowerPoint 1
Translating Excel data into a table in PowerPoint 2: Multiple Slides
Drawing Shapes in Excel
Translating Excel data into Shapes in PowerPoint
CONTROLLING WORD FROM EXCEL
Introduction and Creating an MS Word Invoice from Excel
Gathering Data from MS Word docs in to Excel (Change Control Form)
Purchase order system: part 1 – Excel to word
Purchase order system: part 2 – The word template
Purchase order system: part 3 – Save & print button
Purchase order system: part 4 – Excel to word
THE VBA TOOLS & TEMPLATES VAULT
Resource Analysis: Good practice for managing data
Dynamic Charts
Charting Actual vs Forecast
Segmenting for deeper MI
Meaningful Resource Analysis
Interesting MI Aesthetics, automating folders and PDF files
Automate the creation of a PowerPoint slide deck
Combining PowerPoint with Information Gathering
Automating the production of a Project Summary front page in PowerPoint