Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Main sheet automatically picks up data from other sheets | Excel Worksheet Functions | |||
Updating sheets in Workbook from main sheet | Excel Discussion (Misc queries) | |||
LINKING MAIN SHEET WITH MULTIPLE SUMMARY SHEETS | New Users to Excel | |||
LINKING MAIN SHEET WITH MULTIPLE SUMMARY SHEETS | Excel Worksheet Functions | |||
How do I combine data from several sheets into one main sheet? | Excel Worksheet Functions |