Use a loop that scans each sheet tab and copies its used range to a destination page created in advance. Set the target row counter dynamically so pasted records are stacked without overwriting existing data.
Define the source range through UsedRange or by detecting the last filled row and column. This prevents empty cells from being transferred and keeps the final table compact and readable.
Exclude the destination page from the loop and copy header rows only once. A simple conditional check on the sheet index avoids duplicated column names and keeps the structure consistent across all imported records.
Merging Several Excel Sheets Using a Macro Script
Prepare a destination page before running the macro and assign it a fixed name. This page will receive rows copied from every other sheet tab, keeping all records in a single table layout.
- Loop through each sheet object except the destination page.
- Detect the last filled row using Cells(Rows.Count, 1).End(xlUp).Row.
- Append new rows by tracking the next empty row on the target page.
Transfer column headers only from the first source page. A simple counter variable prevents repeated header insertion and keeps the final dataset clean.
- Read the source range using row and column bounds.
- Paste values only to avoid formatting conflicts.
- Clear clipboard mode after each copy action.
This structure supports large datasets and reduces errors caused by blank rows or mismatched column counts.
Looping Through Sheets to Copy Data to a Target Page
Set a For Each loop that iterates across every sheet tab except the destination page. This ensures each data source is processed once and prevents accidental self-copying.
Identify the active data block by locating the last used row and column on each source page. Referencing these bounds avoids transferring empty cells and keeps row counts accurate.
Maintain a running row index on the target page that updates after each paste action. Appending records sequentially preserves order and avoids overwriting earlier entries.
Insert a conditional check to skip hidden or empty pages. This keeps the macro focused on valid data sources and reduces runtime errors during execution.
Handling Headers Blank Rows and Data Range Detection in Macro Code
Copy column titles only from the first source page and skip them on all others. Use a Boolean flag or counter variable to control header transfer and keep the final table from repeating labels.
Detect blank rows by checking the last filled cell in a key column rather than relying on fixed ranges. Reading row bounds through End(xlUp) prevents gaps from being pulled into the result.
Define the data block by calculating both the final row and final column for each source page. This avoids copying empty columns and keeps pasted records aligned.
Ignore pages where the detected row count equals the header row only. This rule filters out empty or template pages and reduces the risk of inserting useless lines.