Copy Ranges from Multiple Excel Worksheets to One Sheet
Question details
Combine specified ranges from multiple worksheets into one destination 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.
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.
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.
In Excel, open the workbook, select Developer > Visual Basic, and choose Insert > Module in the VBA editor.
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
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). In Excel, select Developer > Macros, choose CombineRanges, and click Run.
Open the Combined sheet and check A1:A36. Update the sheet names or ranges in the module if your workbook uses different sources.

Combine the ranges with a formula
Use a formula when you want the destination to update as source values change, without running a macro.
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. Open the workbook: Open your Excel workbook in WPS Spreadsheet and confirm that the source sheets are named Sheet1, Sheet2, and Sheet3.
- 2. Prepare the destination: Create or open a sheet named Combined and make sure A1:A36 is available.
- 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. 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.

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.




