Previous Lecture Complete and continue  

Unlock Excel VBA & Excel Macros

  Introduction & Scope
  Section 2: Your First Excel Macro
  Section 3: The Object Model
  Section 4: Referencing Ranges, Worksheets & Workbooks with VBA
  Section 5: Working with Variables
  Section 6: Looping through Collections & Making Decisions
  Section 7: Useful Built-in Functions
  Section 8: Debugging, Error Handling & Procedure Scope
  Main Project #1: Create a Tool to Automate Table of Contents (TOC)
  Section 10: Looping in VBA (Controlling the Flow of Code)
  Section 11: Working with Arrays
  Section 12: Working with Files, Folders & Text Files
  Section 13: Excel Tables, Formulas & Pivot Tables
  Main Project #2: Sales Regional Reporting Tool
  Section 15: Interacting with Other Applications (Word, PowerPoint etc.)
  Section 16: Workbook and Worksheet Events
  Section 17: Working with Userforms & ActiveX Controls
  Main Project #3: Invoice Generation Tool
  Section 19: Create your own Custom Functions
  Section 20: Working with Charts
  BONUS! CODE BOOK
  Summary & Final Words

  Activity: Save a Hard-coded Copy of Workbook

Activity: Save a Hard-coded (Formula free) Version of Your Workbook

Use the knowledge you've gained until now to create a macro that saves a hard-coded copy of your Workbook.

The copy should have no formulas - We just want to have a back up of the file without any links or formulas.

The name of the new Workbook should be "HC_" followed by the name of the workbook.

The steps for the macro would be:

  1. Save the current Workbook
  2. Select all sheets, copy and paste special all cells in the sheets
  3. Save the Workbook with Save As (be careful you don't SAVE, but SAVEAS)
  4. Save it in the same directory as the Workbook. Call the new Workbook "HC_" followed by the current name

BONUS: Add today's date in the form of "yymmdd" after "HC_". For example: "HC_yymmdd_S4_ReferencingRanges"

BONUS: #2: Save the copy without macros (i.e. the copied version should be a normal xlsx file)

Note: Instructions are also inside S4_ReferencingRanges_Start File in the Activity tab. Don't forget to save the file as a macro enabled Workbook.

Answer is in the project video (coming up next) and also included in the complete version of this Workbook in Module ProjectActivity. I also solve for the two bonus points in the next video - Just a heads up that I do use some VBA built-in functions here. We cover these in Section 7.

Activity: Save a Hard-coded (Formula free) Version of Your Workbook

Use the knowledge you've gained until now to create a macro that saves a hard-coded copy of your Workbook.

The copy should have no formulas - We just want to have a back up of the file without any links or formulas.

The name of the new Workbook should be "HC_" followed by the name of the workbook.

The steps for the macro would be:

  1. Save the current Workbook
  2. Select all sheets, copy and paste special all cells in the sheets
  3. Save the Workbook with Save As (be careful you don't SAVE, but SAVEAS)
  4. Save it in the same directory as the Workbook. Call the new Workbook "HC_" followed by the current name

BONUS: Add today's date in the form of "yymmdd" after "HC_". For example: "HC_yymmdd_S4_ReferencingRanges"

BONUS: #2: Save the copy without macros (i.e. the copied version should be a normal xlsx file)

Note: Instructions are also inside S4_ReferencingRanges_Start File in the Activity tab. Don't forget to save the file as a macro enabled Workbook.

Answer is in the project video (coming up next) and also included in the complete version of this Workbook in Module ProjectActivity. I also solve for the two bonus points in the next video - Just a heads up that I do use some VBA built-in functions here. We cover these in Section 7.