Summary
An Excel library of 160+ done-for-you macros to do the most common Excel work
Learning goals
In this program you will discover:
Module 1 – How to move data between cells, sheets, and workbooks
- How to copy data from one range to another sheet
- How to copy data to a new workbook and save it
- And much more!
Module 2 – How to write formulas in cells and ranges
- How to write a COUNT formula
- How to write a SUMIF formula
- How to write a SUM formula
- How to copy formulas down to the last row
- And much more!
Module 3 – How to merge cells, sheets, books, etc.
- How to merge two cells that match certain criteria
- How to merge many sheets
- How to merge books in a folder
- And much more!
Module 4 – How to open, save and export workbooks and sheets
- How to save a book with a different name
- How to close all open books
- How to open the books in a folder
- How to send the active sheet as pdf by email
- How to save with a custom stamped name
Module 5 – How to do Vlookups
- How to write a Vlookup formula in Excel
- How to use the power of Vlookup inside a macro (without writing any formula)
- How to write an Index & Match
- And much more!
Module 6 – How to print
- How to print the books in a folder
- How to print the selected sheets
- How to print a range of pages of the active sheet
- And much more!
Module 7 – How to create and set up PivotTables
- How to create a PivotTable from scratch
- How to rename the PivotTable totals
- And much more!
Module 8 – How to create and set up charts
- How to create a line chart
- How to create a pie chart
- How to set the chart title
- How to set the chart axes
- And much more!
Module 9 – How to format cells and ranges
- How to set the cell font styling
- How to set the range borders
- How to merge and unmerge cells
- How to set the cells number formatting
- And much more!
Module 10 – How to loop (how to iterate through cells, books, charts, PivotTables, etc)
- How to loop through the cells of a column
- How to loop through the cells of a row
- How to loop through books, charts, etc
- How to loop through PivotTables
- How to loop through PivotTable fields and items
- And much more!
Module 11 – How to work with cells and ranges
- How to hide columns, rows, etc
- How to write variable values to cells
- How to store a cell value in a variable
- And much more!
Module 12 – How to get information about cells and ranges
- How to store a range in an object
- How to find the last used row
- How to get the size of a range
- How to enter to the next occupied cell
- And much more!
Module 13 – How to create and set up UserForms
- How to create a User form
- How to add the most common controls (text boxes, command buttons, etc.) to user forms
- How to send user form data to the last row of the active sheet
- How to send user form data to the last row of any given sheet
- And much more…
Module 14 – How to connect Excel with Access
- How to import an Access table/query into the current sheet
- How to open Access from Excel
- And much more…
Module 15 – How to work with directories and files
- How to get the path of workbooks and files
- How to create a filesystem object that allows you accessing to any directory using VBA
- How to create a file on a folder
- And much more…
Module 16 – How to work with tables
- How to convert a range into table
- How to filter a field
- How to format a table
- How to remove duplicates
- How to add and remove rows and columns
- How to clear contents
- And much more…
Last update: Feb 12, 2020
-
Getting started
-
How to move data between cells, sheets and workbooks
- Introduction
- How to copy a range from the current sheet to another range of the same sheet
- How to copy a range from the current sheet to another sheet
- How to copy a range from a sheet to another sheet
- How to copy a range from the current workbook to another workbook
- How to copy a range from one workbook to another workbook
- How to copy and paste the seleced cell or range on the same sheet
- How to copy and paste that cell or range on the same sheet (the manual way)
- How to copy data to a new workbook and save it
- How to copy and paste with the paste special command
- How to copy cells from one location (column) in a worksheet to another column (same worksheet) until no more data found in the first column – Method 1
- How to copy cells from one location (column) in a worksheet to another column (same worksheet) until no more data found in the first column – Method 2
- How to capture a cell that meets certain condition and move it to a different worksheet
- How to copy a sheet after a given sheet
- How to copy a sheet after a given sheet and rename it
- How to copy a sheet before a given sheet
- How to move a sheet after a given sheet
- How to move a sheet before a given sheet
- How to copy a sheet to the end
- How to copy the current data region and paste it on another book, sheet
- How to copy a column to another sheet
- How to copy columns to another sheet
- How to copy a row to another sheet
- How to copy the used column to another sheet
- How to move cells to another sheet if the first cell contains a certain value
- How to copy to the last open row on a different sheet
- How to cut selection
- How to cut columns
- How to insert columns to the right of the given column
-
How to use and write formulas in cells and macros
- Introduction
- How to write a SUM formula at the given cell
- How to write a SUM formula at the active cell
- How to write a SUM formula at the bottom of a column
- How to write a SUM formula at the top of a column
- How to fill a new column down to the same range as the previous column
- How to write a formula at the top of a column and fill the new column down to the same range as the previous column
- How to use a range object in a formula
- How to write an Index and Match formula in a cell
- How to use an Index and Match formula in a macro
- How to write a SUMIF formula
- How to write a countif function in a cell
- How to use a countif function in a macro
- How to write a formula in a cell
- How to set up the description of a custom function in the Function dialog
- How to create a custom function
-
How to combine cells, sheets, tables, etc.
-
How to get user data through forms
-
How to open, save and export workbooks and sheets
- Introduction
- How to save as
- How to save as with dialog
- How to save with stamped name
- How to save with custom stamped name
- How to save as csv (comma separated file)
- How to open a book
- How to open books in folder
- How to open a book with dialog
- How to close all the open books and save the changes
- How to send the active book as pdf attached
- How to send the active book as attachment
-
How to do vlookups
-
How to print
- Introduction
- How to print the entire book
- How to print the selected sheets
- How to print the selection
- How to print the visible sheets only
- How to print the current sheet’s pages
- How to print some open books
- How to print the open books
- How to print books if their names meet certain condition
- How to print each open book and close it
- How to print all the books in a directory
- How to set up a page for printing
-
How to create and set up PivotTables
-
How to create and set up charts
- Introduction
- How to create a line chart
- How to create a pie chart method 1
- How to create a pie chart method 2
- How to set up the chart axes
- How to set up the chart title
- How to set up the chart gridlines
- How to set up the chart legend
- How to set up the chart series
- How to set up the chart plot area
- How to set up the chart trend line
-
How to format cells and ranges
- Introduction
- How to remove hyperlinks from sheet
- How to remove formatting from sheet
- How to remove formatting from a range
- How to remove formatting from the selection
- How to copy and paste format
- How to set a style to a table
- How to fit the columns width
- How to fit the rows height
- How to merge and center a range
- How to merge and center the selection
- How to set up the range borders
- How to set the range font styling
- How to set the number format to range
- How to set the number format to selection
- How to set the range filling
- How to set the selection filling
- How to set the conditional formatting of selection
- How to set the conditional formatting of a given range
-
How to loop (iterate through cells, books, charts, etc.)
- Introduction
- How to move cells to another sheet if the cell contains a certain value
- How to remove rows when its entries are duplicated on another sheet
- How to loop through each cell of a column
- How to loop through each cell of a row
- How to remove a row if a cell matches certain criteria
- How to loop through each cell of a row
- How to remove a column if a cell matches certain criteria
- How to loop through books
- How to loop through the sheets of the current book
- How to loop through each cell of a column
- How to loop through charts
- How to loop through the cells of a column and check a condition
- How to loop through chart series
- How to loop through the chart series of all charts
- How to loop through the PivotTables of a worksheet
- How to loop through all the PivotTables of a workbook
- How to loop through PivotTable items
- How to loop through PivotTable fields
-
How to work with cells and ranges
- Introduction
- How to hide selection rows
- How to hide rows
- How to hide multiple rows
- How to unhide rows
- How to unhide all hidden rows
- How to hide columns
- How to hide multiple columns
- How to hide selected columns
- How to unhide selected columns
- How to unhide all hidden columns
- How to unhide rows
- How to select a range
- How to select rows
- How to output a calculation to a cell
- How to store a cell value in a variable
-
How to get information about cells and ranges
- Introduction
- How to store current Range selection
- How to store the current Range selection
- How to store a Range selection
- How to store the current Range selection
- How to count the entries in a column
- How to count the entries in a row
- How to find the last column
- How to enter to next ocupied cell
- How to enter to next empty cell in a consecutive range
- How to select the next ocupied cell at the right
- How to select the next ocupied cell at the left
- How to store the data region in an object variable
- How to get the size of the given range
- How to find the last-used cell in a column
- How to find the last-used cell of a named range
- How to find the last-used cell in the data region
- How to find the last-used cell in a table
-
How to connect Excel with Access
-
How to work with directories and files
- Introduction
- How to get the path of the current workbook
- How to get the full path of the current workbook (path and name)
- How to get the file name of the current workbook
- How to set the current working directory
- How to get the current working directory
- How to create a file system object
- How to get the path of a folder using a dialog
- How to copy a folder
- How to get folder information
- How to create a text file in a folder
-
How to work with tables
- Introduction
- How to convert a range into table
- How to rename a table
- How to convert a table to range
- How to convert the current data region into table
- How to loop through all the items of a column
- How to clear the table rows without killing the table
- How to clear the table contents
- How to remove duplicates from a table
- How to format a table
- How to filter a table
- How to clear the filters from a table
-
Bonus material
Instructor
Free
Is there any VBA template for sending a pre-configured email to a list of people with respective PDF files attached? I use Mail-Merge by compaosing an email body on Word and send it out by merging a list of email addresses in Excel (CSV file). However, I don’t know how to attach respective PDF files to each email. The file name of each PDF file (like invoices) is different sp each email address has to correspond with a specific PDF file name.
Jeffrey look at Power Automate to do this. I haven’t used it myself so can’t help further sorry.
I’ve just seen posts in the MS User Group along those lines.
Hi,
I will upload the new email macros module that includes Jeffrey macros and much more.
Will post the link here when the module goes live.