How to import data from one Excel spreadsheet to another using VBA

It can be very helpful knowing how to import data from one Excel spreadsheet to another using VBA. In fact, one of the new features I added to my College Football Bowl Prediction spreadsheet is the ability to import a user’s picks from a separate entry form into the pool manager’s tracking sheet automatically. I did this by inserting a button in the manager’s file that is linked to a VBA macro. If you're brand new to programming macros in Excel, please see my getting started guide here.

The import spreadsheet VBA code is below. The comments colored green explain what is going on. This code is pretty quick and dirty - no real error handling has been inserted to deal with unexpected situations, something that should be added.

'this code imports a single player's picks into the pool manager 'player's picks must be saved as .xls file '============================================================== Sub Import_Single_Player_Data() ' Get player's workbook... Dim customerBook As Workbook Dim filter As String Dim caption As String Dim customerFilename As String Dim customerWorkbook As Workbook Dim targetWorkbook As Workbook ' make weak assumption that active workbook is the target Set targetWorkbook = Application.ActiveWorkbook ' get the customer workbook filter = " *.xls,*.xls" caption = "Please Select an input file " customerFilename = Application.GetOpenFilename(filter, , caption) Set customerWorkbook = Application.Workbooks.Open(customerFilename) 'define the target sheets in order to copy data from customer to target workbook Dim targetSheet As Worksheet Set targetSheet = targetWorkbook.Worksheets(2) Dim sourceSheet As Worksheet Set sourceSheet = customerWorkbook.Worksheets(1) 'select and copy column L from the new player's spreadsheet sourceSheet.Columns("L:L").Select Selection.Copy 'active the pool manager spreadsheet targetSheet.Activate 'select location where the copied column will be inserted Columns("L:L").Select 'insert the new column and shift all others to the right Selection.Insert Shift:=xlToRight   ' Close customer workbook customerWorkbook.Close End Sub

See how this import spreadsheet macro works by downloading the bowl pool manager template. As I’ve stated earlier, using my completed templates is a great way to figure out how to do things that can be applied to other tasks.

By the way, I copied the code into this blog post with formatting by using Notepad++, one of the programming tools I use everyday.