Have you ever had multiple CSV files that needed to be loaded into the Excel workbook, but you wanted each file to go into a separate worksheet instead of dumping everything into one? If that sounds like you, there’s a quick and efficient way to do it by using a simple Visual Basic Script (VBA). This method can save you loads of time and headache, especially if you’re dealing with large amounts of data. Below, I’ll show you how to get it done.

Why Import Multiple CSV Files Into Separate Worksheets?

When working with multiple CSV files in Excel, it’s common to need each file to go into a separate worksheet. Especially when managing datasets that come in different categories or timeframes. For example, you may have files for each year of an organization’s sales or departments, and it’s important to keep these separate while still analyzing them in the same workbook.

If you manually import them one by one or merge everything into one sheet, that’s a lot of unnecessary work. Thankfully, Excel’s built-in capabilities, combined with a Visual Basic Script, make this process a breeze.

Preparing to Import CSV Files

Step 1: Create a Macro-Enabled Workbook

The first thing you’ll need to do is to create a new Excel workbook that can handle macros, as that’s where the Visual Basic Script will go.

  • Open Excel and navigate to File → Save As.
  • Give your workbook a useful name like “Import_Multiple_CSV_Files_Template”.
  • Select Excel Macro-Enabled Workbook (*.xlsm) from the file type dropdown.

Now that you have your macro-enabled workbook, it’s time to get the script into Excel.

Step 2: Enable the Developer Tab

To write and run macros, you’ll need to access Excel’s Developer tab. If you don’t see it in the ribbon, you can enable it by following these steps:

  • Go to File → Options.
  • Click Customize Ribbon on the left.
  • On the right side, find Developer under Main Tabs and check the box.
  • Click OK to apply.

The Developer tab should now appear in your ribbon, giving you access to Excel’s macro development and Visual Basic tools.

RELATED TOPIC: How to Import Contacts from Excel to Outlook

Entering the Visual Basic Script (VBA)

Step 3: Open the Visual Basic Editor

Now that your workbook is ready, follow these steps to insert the VBA code to put each CSV file into a separate worksheet in Excel:

  • Go to the Developer tab and click on Visual Basic.
  • In the Visual Basic Editor, click Insert → Module. You now have a blank module where the code will go.

Step 4: Paste the Script

Simply copy it and paste the VBA Code below into the blank module in the Visual Basic Editor. Once pasted, go ahead and save the module.

VISUAL BASIC CODE:

Sub CombineCsvFiles()
‘updated by MelCompton
Dim xFilesToOpen As Variant
Dim I As Integer
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
Dim xScreen As Boolean
On Error GoTo ErrHandler
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
xDelimiter = “|”
xFilesToOpen = Application.GetOpenFilename(“Text Files (*.csv), *.csv”, , “MelCompton VBA for Excel”, , True)
If TypeName(xFilesToOpen) = “Boolean” Then
MsgBox “No files were selected”, , “MelCompton VBA for Excel”
GoTo ExitHandler
End If
I = 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Copy
Set xWb = Application.ActiveWorkbook
xTempWb.Close False
Do While I < UBound(xFilesToOpen)
I = I + 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Move , xWb.Sheets(xWb.Sheets.Count)
Loop
ExitHandler:
Application.ScreenUpdating = xScreen
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , “MelCompton VBA for Excel”
Resume ExitHandler
End Sub

Here’s a quick breakdown of what the VBA script does:

  • It grabs your selected CSV files.
  • It creates a new worksheet for each file.
  • It names each worksheet according to the file name.
  • It imports the data from each CSV into its corresponding worksheet.

After saving, exit the Visual Basic Editor.

RELATED TOPIC: How to Define and Use Cell Names in Excel Formulas and Functions

Running the VBA Script to Import CSV Files

Now it’s time to run the script and get all your CSV files into separate worksheets in Excel.

Step 5: Run the Macro

  • On the Developer tab, click on Macros.
  • You should see a macro name like CombineCSVFiles (or whatever name the script provided). Select it and click Run.
  • When the file popup appears, navigate to the folder where your CSV files are stored.
  • You can select a few files by holding Ctrl or select all of them using Ctrl+A.
  • Once you’ve made your selections, click Open.

After a few seconds, you’ll see Excel create a new workbook, and each CSV file will have its own tab!

Step 6: Save Your Results

Now that you have all your CSV data loaded into separate tabs, you can save the workbook with a new name. Go to File → Save As, and choose a distinct file name and the location where you want to store it.

Customizing Import Settings

This VBA script works with more than just CSV files. It will import any kind of delimited text file, whether it’s a .txt or .csv file format. The most important thing is that your files are saved in a compatible format that Excel recognizes.

Additionally, after importing, you can format or modify the data as needed. Whether it’s adding conditional formatting, creating pivot tables, or visualizing the data in charts, once those sheets are in Excel, the sky’s the limit.

Handling Future Imports

Once you’ve created your macro-enabled workbook, you can use this file over and over again for similar imports. Make this even easier by keeping the workbook saved as a template file and using it whenever new batches of CSVs need to be processed.

Next time you open the template, just remember:

  • Re-enable macros if prompted with a security warning (you’ll typically see a yellow bar at the top of the screen. Just click Enable Content).
  • Run your CombineCSVFiles macro from the Developer tab.
  • Select your CSV files and you’re good to go.

RELATED TOPIC: How to Use the 3D Reference in Excel

Conclusion

Using this Visual Basic Script in Excel is a time-saver when it comes to importing multiple CSV files into separate tabs in the same workbook. It’s an easy and fast method that’s especially helpful when handling large datasets split across different files.

Whether you need this for personal finance tracking, project management, or any other work that involves numerous CSV files, this trick is a must-have in your Excel tool kit.

Drop a comment below if you have any questions or feedback, and don’t forget to hit that subscribe button to stay updated on more Excel tips and tricks!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR