copy from multiple sheets

[paul mueller]

I need to get a list from each worksheets "B"column, then fill a listbox
with unique items from that list, when an item is selected, to match it to
all matching items within column "B" on each sheet and copy the row that the
matching item is on to Sheet1.
I have tried using the code written from http://www.rondebruin.nl/copy5.htm
to try and make it work for me, but still no luck.

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim range As range

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Main").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
DestSh.Name = "Main"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
range("B2", range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Sheet1.range("A:A"),
Unique:=True
End If
Next
Cells(1).Select
Application.ScreenUpdating = True
End If

 

[Bernie Deitrick]

Paul,

The code below will do what I think you want to do: copy unique items from
each sheet's column B and place them in column A of a new sheet named Main.

Note that if the headers of each of the column B's are the same, you will
get multiple copies of the header value in your list. The code can be
modified to change that behavior, of course.

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim range As range

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("Main").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Set DestSh = Worksheets.Add
DestSh.Name = "Main"

Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Activate
If sh.Name <> DestSh.Name Then
sh.range("B2", sh.range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=sh.range("B65536").End(xlUp)(3), _
Unique:=True
sh.range(sh.range("B65536").End(xlUp), _
sh.range("B65536").End(xlUp).End(xlUp)).Cut _
DestSh.range("A65536").End(xlUp)(2)
End If
Next sh
Cells(1).Select
Application.ScreenUpdating = True

End Sub

 

[Ron de Bruin]

Hi Paul

Use this example
http://www.rondebruin.nl/copy2.htm
To collect all cells in one sheet

Use Advanced Filter to get you unique list then
http://www.contextures.com/xladvfilter01.html#FilterUR


Look at this also
http://www.j-walk.com/ss/excel/tips/tip47.htm