PDA

View Full Version : Loop filter and copy



molcy82
10-15-2012, 11:46 PM
Hi all,

First of all, Let me express what I want to do.

There are outlet numbers on cell A1 which you can select then press F9 to calculate.

In the attached excel file there is a P&L statement on the first sheet called "PAR TABLE" which gathers data after you select the outlet number through sumif function from the sheet WKZKA.

What I want is the copy of 5 of these sheets to a new work book for every single outlet number. But special treatmetn is needed pn WKZKA sheet. I want only filltered outlet data for each excell file.

I try to simply it throug VBA code for your understanding

Sub LOOP1()

Do
Sheets(1).Activate
ActiveCell.Offset(1, 20).Select 'Beginning from T001
Calculate
Sheet(5).Activate
'go to column D and select t001 filter only selected items then
Range("d1").AutoFilter Field:=1, Criteria1:="ActiveCell.Offset(1, 20).value"
'selects only visible cells then
Range("d1:d" & Cells(Rows.Count, "d").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
'copy visible cells sheet to new work book then
'copy other sheets to new workbook
'rename the sheets if necessary
'close the sheet
'loop until all T00x codes is emtpy

End Sub

I will appreciate if you can help me how to do it.

With many thanks.

Regards