Merging Data From Muliple Sheets

LeeHitchen

New Member
Joined
Mar 20, 2002
Messages
26
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
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.
 

jakemelon

Board Regular
Joined
Mar 14, 2003
Messages
76
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
 

Forum statistics

Threads
1,144,208
Messages
5,723,032
Members
422,476
Latest member
beck85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top