Merging Data From Muliple Sheets

[LeeHitchen]

Hi All

I have a number of worksheets containg 5 digit numbers located in column A. Some of these numbers are duplicated ie appear on more than one sheet. Is there a quick way to 'merge' these numbers from all worksheets ending up with a distinct list? At the moment I'm cutting and pasting the numbers from each sheet until I end up with one long list and then filtering to end up with a distinct list. However, as you can imagine, with a large number of worksheets this is more than a little tedious.

Thanks for your help.

Lee

 

[rikrak]

On 2002-09-13 01:24, LeeHitchen wrote:
Hi All

I have a number of worksheets containg 5 digit numbers located in column A. Some of these numbers are duplicated ie appear on more than one sheet. Is there a quick way to 'merge' these numbers from all worksheets ending up with a distinct list? At the moment I'm cutting and pasting the numbers from each sheet until I end up with one long list and then filtering to end up with a distinct list. However, as you can imagine, with a large number of worksheets this is more than a little tedious.

Thanks for your help.

Lee

Hi,
You can use this macro to copy cells A1:A5 from all sheets into your active sheet:
Sub copyFromAllSheets()
Application.ScreenUpdating = False
'loop thrue all sheet
For Each sheet In Sheets
'skip active sheet
If sheet.Name <> ActiveSheet.Name Then
sheet.Range("A1:A5").copy
'find last empty cell in column A
Range("A65536").End(xlUp).Offset(1, 0).Select
'paste the copied data
ActiveSheet.Paste
End If
Next
Application.ScreenUpdating = True
End Sub

Please try this on a copy of your workbook to see if this is what you need.

 

[LeeHitchen]

Rikrak

Just the ticket, thanks for your help.

Regards

Lee

 

[jakemelon]

how would I modify this macro so that it merges all worksheets from an outside workbook into the active sheet?

for those of you who followed my earlier post I do plan on merging everything like everyone recommended after all! thank you!

thanks
j