Excel copy data from multiple worksheets

Question

Is there a way to copy cell ranges from multiple worksheets into another worksheet? For example:
Sheet1
Apple
Grapes
Peach
Cherry
Sheet2
Orange
Pear
Banana
Blueberry

I need the result as

Sheet3
Apple
Grapes
Peach
Cherry
Orange
Pear
Banana
Blueberry

Assume I have the data in Column A in the Sheet 1 & 2 and I need to display the combined results in Column A on Sheet3. Basically I need to display all items from Sheet 1 & 2 in Sheet3 in one column.

Any ideas? Thanks in advance.

aevanko aevanko · Accepted Answer · 2011-07-26T03:48:42

Since your data is in the same columns, I am assuming all you really need to do is copy paste each sheet into one master sheet. This VBA function I found a while back on the web (so sorry to the original creator, I wish I knew who it was so I can credit him). It will combine all worksheets into one worksheet called "Master". Saves a lot of time! I hope this helps or gets you close to where you need to be.

Sub CreateMasterSheet()

Application.ScreenUpdating = False
Dim wrk As Workbook
Dim sheet As Worksheet
Dim masterSheet As Worksheet
Dim rng As range

Set wrk = ActiveWorkbook

For Each sheet In wrk.Worksheets
    If sheet.Name = "Master" Then
        MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
                "Remove or rename this worksheet.", vbOKOnly + vbExclamation, "Error"
        Exit Sub
    End If
Next sheet

Set masterSheet = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.count))
masterSheet.Name = "Master"

For Each sheet In wrk.Worksheets
    If sheet.Index = wrk.Worksheets.count Then
        Exit For
    End If
    Set rng = sheet.range(sheet.cells(1, 1), sheet.cells(65536, 1).End(xlUp).Resize(, 256))
        masterSheet.cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.count, rng.Columns.count).Value = rng.Value
Next sheet

masterSheet.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Excel copy data from multiple worksheets

2 Answers