Microsoft Excel Macro
Register for Microsoft Excel Macro training today! Doing your macro tasks will be never be the same again.
How do you automate a repetitive task in Excel, so that you can do the same task again in a single click?
Our course in Microsoft Excel Macro is a supplementary course for Microsoft Excel, which will take students through the process of creating and recording macros, to seamlessly automate calculations in your Excel style sheets.
Micromanaging your task with Excel
Who Should Attend
-
- Accounts Clerks
- Accounts Assistants
- Accounts Supervisors
- Office Administrators
- Administrative Assistants
- Secretaries
- Accounting Personnel
- For Managers, or potentially to be managers, across all business units who have little or no formal education in organizational finance and accounting for a better understanding and how to support and enhance revenue generation in their businesses
COURSE OUTLINE
Getting Started with Excel Macros
- Becoming Familiar with Macro Recording Basics
- Comparing Absolute and Relative Macro Recording
- Looking at Other Macro Recording Concepts
- Working in the Visual Basic Editor
- Working with the Project window
- Working with a Code Window
- Customising the VBA Environment
- Discovering the Excel Object Model
- Taking a Brief Look at Variables
Working with workbooks
- Creating a new workbook from scratch
- Saving a workbook when a particular cell is changed
- Saving a workbook before closing
- Protect a worksheet on workbook close
- Unprotect a worksheet on workbook open
- Open a workbook to a specific tab
- Opening a specific workbook defined by the user
- Determine whether a workbook is already open
- Determine whether a workbook exists in a directory
- Refresh all data connections in workbook on open
- Close all workbooks at once
- Open all workbooks in a directory
- Print all workbooks in a directory
- Preventing the workbook from closing until a cell is populated
- Create a backup of a current workbook with today’s date
Automating worksheet tasks with Macros
- Add and name a new worksheet
- Delete all but the active worksheet
- Hide all but the active worksheet
- Unhide all worksheets in a workbook
- Moving worksheets around
- Sort worksheets by name
- Group worksheets by colour
- Copy a worksheet to a new workbook
- Create a new workbook for each worksheet
- Print specified worksheets
- Protect all worksheets
- Unprotect all worksheets
- Create a table of contents for your worksheets
- Zooming in and out of a worksheet with double-click
- Highlight the active row and column
Selecting and modifying ranges
- Selecting and formatting a range
- Creating and selecting named ranges
- Enumerating through a range of cells
- Select and format all named ranges
- Inserting blank rows in a range
- Unhide all rows and columns
- Deleting blank rows
- Deleting blank columns
- Select and format all formulas in a workbook
- Find and select the first blank row or column
- Apply alternate color banding
- Sort a range on double-click
- Limit range movement to a particular area
- Dynamically set the print area of a worksheet
Working with data
- Copy and paste a range
- Convert all formulas in a range to values
- Perform the text to columns command on all columns
- Convert trailing minus signs
- Trim spaces from all cells in a range
- Truncate zip codes to the left five
- Padding cells with zeros
- Replace blanks cells with a value
- Append text to the left or right of your cells
- Create a super data cleanup Macro
- Clean up non-printing characters
- Highlight duplicates in a range of data
- Hide all rows but those containing duplicate data
- Selectively hide autofilter drop-down arrows
- Copy filtered rows to a new workbook
- Create a new sheet for each item in an autofilter
- Show filtered columns in the status bar
Working with pivottables
- Create a backwards-compatible pivottable
- Refresh all pivottables workbook
- Create a pivottable inventory summary
- Make all pivottables use the same pivot cache
- Hide all subtotals in a pivottable
- Adjust all pivot data field titles
- Set all data items to sum
- Apply number formatting for all data items
- Sort all fields in alphabetical order
- Apply custom sort to data items
- Apply pivottable restrictions
- Apply pivot field restrictions
- Automatically delete pivot table drill-down sheets
- Print pivot table for each report filter item
- Create new workbook for each report filter item
- Transpose entire data range with a pivottable
Manipulating charts with Macros
- Resize all charts on a worksheet
- Align a chart to a specific range
- Create a set of disconnected charts
- Print all charts on a worksheet
- Label first and last chart points
- Colour chart series to match source cell colours
- Colour chart data points to match source cell colors
E-mailing from Excel
- Mailing the active workbook as an attachment
- Mailing a specific range as attachment
- Mailing a single sheet as an attachment
- Send mail with a link to our workbook
- Mailing all e-mail addresses in our contact list
- Saving all attachments to a folder
- Saving certain attachments to a folder
Integrating Excel and other office applications
- Running an access query from Excel
- Running an access Macro from Excel
- Opening an access report from Excel
- Opening an access form from Excel
- Compacting an access database from Excel
- Sending Excel data to a word document
- Simulating mail merge with a word document
- Sending Excel data to a PowerPoint presentation
- Sending all Excel charts to a PowerPoint presentation
- Convert a workbook into a PowerPoint presentation
*16 pax per class (max)
Interested in this Course?