Loop through sheets & filter main sheet using sheet name

[Les]

Hello all, as i am not a programmer i am battling with the following...
I have a wkb with the main sheet "Hauptseite-1" in this sheet i have a
variable amount of rows with the names of the responsible person in column
"O". I have created a sheet for each name & the sheet is named with the
applicable name.
I now need to loop through all sheets, with the exception of "Hauptseite-1"
and filter column "O" on the sheet "Hauptseite-1" using the sheet Names as
the filter criteria, copy the data found and past it onto the respective
sheet.

I would be very gratefull for any help.

 

[Joel]

Not sure where you want to paste the data. I wrote the data to the entire
sheet.

Sub FilterDate()


For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Hauptseite-1" Then
With Sheets("Hauptseite-1")
If .FilterMode = True Then
.ShowAllData
End If
Columns("O").AutoFilter field:=1, Criteria1:=sht.Name
.Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=sht.Cells
End With
End If
Next sht
If Sheets("Hauptseite-1").FilterMode = True Then
Sheets("Hauptseite-1").ShowAllData
End If
End Sub

 

[Sam Wilson]

Hi Les,

It might be easier if you copy the rows you want into the named spreadsheets
rather than copy all rows and filter them.


I'm free-typing this so you'll need to de-bug it.

Sub demo()

dim ws as worksheet
dim s as string
dim i as integer
dim j as integer

for each ws in worksheets
if not ws.name = "Hauptseite-1" then
s = ws.name
do until isempty(worksheets("Hauptseite-1").range("o2").offset(i,0))
if worksheets("Hauptseite-1").range("o2").offset(i,0).value = s then
worksheets("Hauptseite-1").range("o2").offset(i,0).entirerow.copy
ws.range("a1").offset(j,0).entirerow.paste
j=j+1
end if
i=i+1
loop
end if
i=0
j=0
next ws




end sub

 

[Les]

Hi Joel, it is copying to the correct sheet, but need the filter in row 11
and need to copy from A11 and all data.

Thanks you for the help

 

[Les]

Thanks Sam, i think the quickest way would be best...

Not sure which one that would be ??