Loop through sheets & filter main sheet using sheet name
 

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.
--
Les

Loop through sheets & filter main sheet using sheet name
 

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

"Les" wrote:

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.
--
Les

Loop through sheets & filter main sheet using sheet name
 

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" wrote:

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.
--
Les

Loop through sheets & filter main sheet using sheet name
 

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


"Joel" wrote:

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

"Les" wrote:

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.
--
Les

Loop through sheets & filter main sheet using sheet name
 

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

Not sure which one that would be ??

--
Les


"Sam Wilson" wrote:

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" wrote:

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.
--
Les