If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
AdvFilterRepFiltered.zip
I found a very important file [http://www.contextures.
com/AdvFilterRepFiltered.zip] at http://www.contextures.com/excelfiles.html#Filter by Debra Dalgleish. Please help me with this:- I want to delete 'Column C' in all new worksheets (these names are already in the new worksheet names) OR how can we generate the new worksheets which do not contain 'Column C' i.e. only other Columns' data gets copied and not the one according to which Filter is applied. I will greatly appreciate if somebody can email me a modified excel file. Thanks a lot. Sushil Raina |
#2
|
|||
|
|||
AdvFilterRepFiltered.zip
You can change the code so it adds headings that don't include the
column C heading. Then use the headings as the extract range in the code. The revised section for the example is: '================== For Each c In Range("J2:J" & r) 'add the rep name to the criteria area ws1.Range("L2").Value = c.Value 'add new sheet (if required) 'and run advanced filter If WksExists(c.Value) Then Sheets(c.Value).Cells.Clear ws1.Range("A1:B1,D1:G1").Copy Destination:=Sheets(c.Value).Range("A1") rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _ CopyToRange:=Sheets(c.Value).Range("A1:F1"), _ Unique:=False Else Set wsNew = Sheets.Add wsNew.Move After:=Worksheets(Worksheets.Count) wsNew.Name = c.Value ws1.Range("A1:B1,D1:G1").Copy Destination:=wsNew.Range("A1") rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _ CopyToRange:=wsNew.Range("A1:F1"), _ Unique:=False End If Next '============== sushil raina wrote: I found a very important file [http://www.contextures. com/AdvFilterRepFiltered.zip] at http://www.contextures.com/excelfiles.html#Filter by Debra Dalgleish. Please help me with this:- I want to delete 'Column C' in all new worksheets (these names are already in the new worksheet names) OR how can we generate the new worksheets which do not contain 'Column C' i.e. only other Columns' data gets copied and not the one according to which Filter is applied. I will greatly appreciate if somebody can email me a modified excel file. Thanks a lot. Sushil Raina -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Thread Tools | |
Display Modes | |
|
|