VBA & Macro Problems

How to Import Excel Files into Separate Worksheets Using VBA

Chanuka Geekiyanage Oct 1, 2026 868 views

Question details

The user wants to automate the process of importing multiple Excel files from a specific folder into individual worksheets within a single master workbook, preserving the original formats and renaming each tab to match its source file name.

How to Import Excel Files into Separate Worksheets Using VBA

Product: Excel
Device & OS: not provided
Scenario: Consolidating data from multiple external workbooks into one central file without manually copying, pasting, and renaming sheets.
Observed behavior: To achieve this, the user needs to write a VBA import macro, attach it to a command button for easy execution, and save the result as a macro-enabled workbook.

Before you start

Before running any macro, ensure that you have enabled the Developer tab in your Excel ribbon and have placed all target Excel files into a single, dedicated folder. It is highly recommended to test the macro on dummy files first to prevent accidental data loss or workbook corruption.

Solution 1Recommended

Create a VBA Macro to Import and Rename Worksheets

Write and execute a custom VBA script that loops through a target folder, copies the content of each file into a new worksheet, and renames the worksheet based on the file name.

This approach requires saving your file as an Excel Macro-Enabled Workbook (.xlsm). Make sure your folder only contains the Excel files you wish to import to avoid script errors.

Open the VBA Editor

Navigate to the Developer tab on the ribbon and click 'Visual Basic', or press Alt + F11 on your keyboard.

Insert a New Module

In the VBA editor, right-click your workbook name in the Project Explorer window on the left, select 'Insert', and then click 'Module'.

Add the Import Code

Paste your VBA code into the module window. Ensure the code utilizes the Dir function to loop through your specific folder path (e.g., C:\MyFolder\*.xlsx) and uses the Worksheets.Add method to create new tabs.

Save as a Macro-Enabled Workbook

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown menu.

Create a VBA Macro to Import and Rename Worksheets

Automation Complete: Running this macro will instantly populate your workbook with distinct, properly named sheets preserving all original formatting.

Solution 2

Assign the Import Macro to a Command Button

Create a clickable button within your worksheet to easily trigger the import macro without needing to open the Visual Basic Editor.

Consolidate Spreadsheets Easily with WPS Office

WPS Office provides robust built-in support for VBA macros (WPS Macro), allowing you to seamlessly import, merge, and rename worksheets just like in Microsoft Excel. It handles complex scripts smoothly and is fully compatible with macro-enabled files.

1. Enable Developer Tools: Open WPS Spreadsheet, navigate to the Tools tab, and ensure the Developer tools are active.
2. Launch the VBA Editor: Click on 'Visual Basic Editor' or press Alt + F11 to open the macro programming environment.
3. Insert and Run Script: Insert a new Module, paste your folder-import VBA code, and run it directly to consolidate your files.
4. Add a Trigger Button: Use the Insert menu to place a Form Control button in your spreadsheet and assign your script to it for one-click execution.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro formatsSupports standard VBA scripts for complex office automationLightweight application with incredibly fast processing speedClean, intuitive interface with built-in developer tools

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro only importing the first file in the folder?

This usually happens if the Dir function is not called correctly inside the loop. Make sure your loop includes a line like 'fileName = Dir()' at the end of the loop structure to advance to the next file in the directory.

How can I ensure the original cell formatting is preserved during import?

Instead of transferring values directly (e.g., Sheet1.Range.Value = Sheet2.Range.Value), use the Copy method in your VBA script (e.g., SourceRange.Copy DestinationRange). This carries over formulas, background colors, fonts, and borders from the source workbook.

How do I fix the 'That name is already taken' error when renaming worksheets?

Excel requires all sheet names within a single workbook to be unique. If multiple source files have the same name, or if a sheet with that name already exists, the macro will crash. You can fix this by adding an error-handling routine in your code or by appending a timestamp or incrementing number to the new sheet's name.

Excel & Data Problems