close
You are not logged in
close
This Month Specials
 

Special Promotion

$199 PowerPoint for Business Blowout! (Normally $299/ea)

PowerPivot Fundamentals Level 5a $199 (Normally $299)

15% off all Web Design Classes

$199 QuickBooks Level 1 or Level 2 for PC, Mac, or Online Classes (Normally $299/ea)

Now $545-Excel Power Programming with VBA (Normally $659)

Now $649-Photoshop Essentials 2 Day Course (Normally $749)

(book before 12/31/16)

GIFT CERTIFICATES AVAILABLE

Current Sales Packages

close
1 Union Square West, Suite 805, NY, NY 212.658.1918

Automating Excel Reports

$299

    Show more

    CALL 212.658.1918 OR EMAIL US

    Automating Excel Reports

    Excel 2010 Training Class Logo

    The Automating Excel Reports Class concentrates on how to automate repetitious Excel tasks and reports by using predefined VBA snippets. The idea is to learn how to utilize recording and playing macros along with important code pieces. This class is geared towards non-technical business people who want to be able to automate reports without having to learn an entire programming language. By the end of this Excel training course, you will be able to automate reports, converting raw data dumps into formatted reports, pivot tables and charts. This Excel class is perfect for those consistently working with Excel who need to automate repetitive tasks. Most Finance and Accounting find this class saves many hours of work each week.

     

     

    Call 212.658.1918 or Email us

     

     

    Materials

    • Customized Manual
    • VBA Snippet reference
    • Multiple Excel XLSM examples for in class and after class

    Course Outline

    MACROS

    • What are macros
    • How to record macros
    • How to run macros
    • How Macros are related to VBA

    VBA Editor

    • What is VBA
    • How to edit VBA Code
    • What is a “sub” routine
    • What is Project Explorer
    • How to turn on Project Explorer
    • Excel Objects and Modules
    • What are Excel Objects
    • What are Modules
    • How to start and stop
    • Debugging Macros

    Exercise

    • Cleaning Raw Accounting data
    • Deleting unused columns and rows
    • Inserting a formatted heading and current date
    • Finding and replacing
    • Inserting proper heading rows
    • Combining multiple Macros

    What are Variables

    • How to declare variables
    • Using find last row snippet
    • Creating a last row variable

    Exercise

    • Concatenating a range
    • Capturing the last row of any data set
    • Selecting a range from the first row to the last row variable
    • Concatenating the last row variable into the VBA

    Practice Exercise

    • Creating a Table from an unknown amount of rows
    • Automating formulas
    • Adding a nested ifferror & vlookup formula to a Table Column

    Macro Creation

    • Adding a Vlookup formula that references a master account name list
    • Creating a Pivot Table from an unknown amount of rows

    Important code Snippets

    • How to capture the name of the current worksheet
    • How to capture the name of the current document

    Exercise

    • Copying a data set and pasting it at the end of a preexisting range
    • Using Preexisting Snippets
    • Using Close and Reopen without Saving Snippet to expedite Macro creation
    • Using Delete Criteria Row Snippet
    • Deleting an entire row based on a cells criteria for a large range
    • Doing something to every worksheet in a workbook
    • Doing something to every document in a folder

    Exercise

    • Copying new data and appending it to a master list
    • Copying new data from multiple worksheets and appending it to a master list

     

    © NYIM Training 2017

    Client Testimonials