Macro to Combine Worksheets

[HJC]

I have 3 worksheets with lists of names. I want to combine the lists into one on a separate worksheet. Is there a way to do this without manually cutting & pasting. The consolidate worksheet functions does not work since I do not need to apply a summary function. The workbook consolidation wizard requires a master workbook and I want to use the same workbook. Any help would be appreciated - VBA novice.

[davidtrickett]

Try this:

Sub mergedata()
Sheets(1).Activate
lastrow = ActiveSheet.UsedRange.Rows.Count
For Each sheet In Sheets
If sheet.Index <> 1 Then
rowcount = sheet.UsedRange.Rows.Count
sheet.UsedRange.Copy Destination:=Sheets(1).Cells(lastrow + 1, 1)
lastrow = lastrow + rowcount
sheet.UsedRange.Clear
End If
Next sheet
End Sub

This assumes that you want to move everything from sheet 2 onwards to sheet 1, and that you want the data below the previous.

To test it I suggest that you leave out the

"sheet.UsedRange.Clear"

line, so you won't lose the contents of the other sheets if something goes wrong

Hope this helps.