VBA & Macro Problems

Copy Ranges from Multiple Excel Worksheets to One Sheet

Maira MehtabMaira Mehtab Nov 18, 2026 870 views

Question details

Combine specified ranges from multiple worksheets into one destination sheet.

Copy Ranges from Multiple Excel Worksheets to One Sheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to copy Sheet1!B2:B10, Sheet2!C2:C20, and Sheet3!F8:F15 into a single worksheet in one operation.
Observed behavior
Excel does not normally copy noncontiguous ranges from different worksheets as one standard selection.
Before you start

Confirm that the source sheet names and ranges match your workbook, and create a destination sheet named Combined. The examples below write to Combined!A1:A36, so make sure those cells can be replaced.

Solution 1Recommended

Use a VBA macro to copy the ranges in one run

Run one macro to copy each source range below the previous range on the destination sheet.

The macro copies the cells and their formatting to Combined, starting at A1. It overwrites the destination cells each time it runs.

1
Open the VBA editor

In Excel, open the workbook, select Developer > Visual Basic, and choose Insert > Module in the VBA editor.

2
Add the macro

Paste this code into the module: Sub CombineRanges() Dim dest As Worksheet Dim src As Range Dim nextRow As Long Set dest = ThisWorkbook.Worksheets("Combined") nextRow = 1 Set src = ThisWorkbook.Worksheets("Sheet1").Range("B2:B10") src.Copy Destination:=dest.Cells(nextRow, 1) nextRow = nextRow + src.Rows.Count Set src = ThisWorkbook.Worksheets("Sheet2").Range("C2:C20") src.Copy Destination:=dest.Cells(nextRow, 1) nextRow = nextRow + src.Rows.Count Set src = ThisWorkbook.Worksheets("Sheet3").Range("F8:F15") src.Copy Destination:=dest.Cells(nextRow, 1) End Sub

3
Save and run

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). In Excel, select Developer > Macros, choose CombineRanges, and click Run.

4
Check the result

Open the Combined sheet and check A1:A36. Update the sheet names or ranges in the module if your workbook uses different sources.

Use a VBA macro to copy the ranges in one run
Formula references may change: The macro uses Excel's normal copy behavior. If source cells contain formulas, relative references may adjust when copied.
Combine worksheet ranges

Consolidate worksheet ranges in WPS Spreadsheet

Use an INDEX formula and Fill Down in WPS Spreadsheet to bring ranges from three sheets into one column. The results update when the source values change.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and confirm that the source sheets are named Sheet1, Sheet2, and Sheet3.
  2. 2. Prepare the destination: Create or open a sheet named Combined and make sure A1:A36 is available.
  3. 3. Enter the formula: Select Combined!A1 and enter =IF(ROW()<=9,INDEX(Sheet1!B2:B10,ROW()),IF(ROW()<=28,INDEX(Sheet2!C2:C20,ROW()-9),INDEX(Sheet3!F8:F15,ROW()-28))).
  4. 4. Fill the destination range: Select A1:A36 using the Name Box and press Ctrl+D to fill the formula down. Check the resulting values on the Combined sheet.
Combine ranges from multiple worksheets in one destination columnKeep consolidated values linked to their source cellsOpen and edit Microsoft Excel XLSX workbooksUse a familiar spreadsheet interface with free access
microsoft office alternative - wps office

Frequently Asked Questions

Can I select and copy ranges from different Excel sheets at once?

Excel does not normally copy ranges from different worksheets as one standard selection. Use the VBA macro to copy them in one run, or use a formula to combine their values.

How do I show the Developer tab in Excel?

In Excel for Windows, select File > Options > Customize Ribbon, check Developer under Main Tabs, and select OK. You can then open Developer > Visual Basic.

Will the combined values update when the source sheets change?

The formula method links to the source cells and updates when they change. The macro copies cells when you run it, so run it again to refresh copied values.

Why does a blank source cell appear as zero?

A formula reference to an empty source cell can display zero. If blanks must remain visually blank, adjust the formula to test for an empty source cell before returning its value.