Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

LinkedIn Learning

Excel VBA: Managing Files and Data

via LinkedIn Learning

Overview

Learn how to automate complex tasks and get more meaningful insights from data in Excel using Visual Basic for Applications (VBA) code.

Syllabus

Introduction
  • Automate processes using Visual Basic for application code
  • What you should know
1. Focusing Data Using Filters
  • Select the active region
  • Create a filter
  • Chain criteria using AND
  • Combine criteria using OR
  • Find unique items using xlFilter
  • Restore a range or worksheet to an unfiltered state
2. Managing Files Using VBA
  • Determine if a workbook exists
  • Determine if a folder (directory) exists
  • Detect whether a file is open
  • Open a workbook
  • Close a workbook
  • Save a workbook under a new name
  • Save a workbook as a CSV file
3. Managing Worksheets Using VBA
  • Check if a worksheet exists
  • Create and rename worksheets
  • Copy a worksheet within the active workbook
  • Copy a worksheet to a new workbook
  • Copy a worksheet to an existing workbook
  • Move a worksheet within the active workbook
  • Move a worksheet to a new workbook
  • Move a worksheet to an existing workbook
4. Taking Advantage of Built-In Functions
  • Use the built-in Open dialog box
  • Suppress and restore alerts
  • Calculate data using Excel worksheet functions
  • Use the current date and time
  • Remove spaces from before or after a string
5. Managing Charts Using VBA
  • Create a chart
  • Move a chart to a chart sheet
  • Add or remove data series from a chart
  • Export a chart as an image
  • Create a line sparkline
  • Create a column sparkline
  • Create a win/loss sparkline
  • Delete a sparkline
6. Creating UserForms
  • Create a UserForm
  • Add a TextBox to a UserForm
  • Add a ListBox to a UserForm
  • Add a ComboBox to a UserForm
  • Add an option button to a UserForm
  • Add graphics to a UserForm
  • Add a SpinButton to a UserForm
  • Create a multipage or multitab UserForm
  • Write UserForm data to a worksheet
  • Run a UserForm
7. Managing PivotTables Using VBA
  • Record a PivotTable configuration
  • Assign a PivotTable macro to the Quick Access Toolbar
  • Advance one step forward in a PivotTable playlist
  • Move to any PivotTable playlist position
Conclusion
  • Next steps

Taught by

Curt Frye

Reviews

Start your review of Excel VBA: Managing Files and Data

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Someone learning on their laptop while sitting on the floor.