Excel-Filtered Results in Single New Sheet

[abc007]

Dear Excel Geeks,

I have a request pretty close to the one mentioned in this thread.
http://www.excelforum.com/excel-prog...ml#post2399345

I have two other requests.

1. The first one is very similar to the one you answered. The only change is that I want the data to be pasted under a heading instead of adding a new sheet each time.

Please see my excel sheet "MS Excel-Filtered Results in Single New Sheet.xls" for better understanding.

2. Also the results that are pasted in a new excel sheet as shown in above example need to be saved as text files with the top name as separate text files, as shown in zip file "MS Excel-Filtered Results in New Text Files.zip".

Please see my example text files for better understanding.

Thanks in advance for the help!

Pavan

[JBeaucaire]

This is similar only in that you want stuff copied from one sheet to another. There is little similar beyond that.

For your need #1, here's a simple macro to accomplish that:

Option Explicit

Sub ParseToColumnarLists()
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      10/14/2010
'Summary:   Two column table on input sheet is parsed into list columns on a second sheet
Dim wsData As Worksheet:    Set wsData = Sheets("Input")
Dim wsOut As Worksheet:     Set wsOut = Worksheets.Add(After:=Sheets(Sheets.Count))
Dim Locations As Range
Dim Loc As Range
Application.ScreenUpdating = False

wsData.Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=wsOut.Range("a1"), Unique:=True
With wsOut
    With .Range("A2:A" & Rows.Count).SpecialCells(xlConstants)
        .Copy
        wsOut.Range("A1").PasteSpecial xlPasteAll, Transpose:=True
        .Clear
    End With
    .Columns.AutoFit
    .Range("Extract").Name.Delete
    
    Set Locations = .Rows(1).SpecialCells(xlConstants)
    
    wsData.Rows(1).AutoFilter
    For Each Loc In Locations
        wsData.Rows(1).AutoFilter Field:=1, Criteria1:=Loc
        wsData.Range("B2:B" & Rows.Count).Copy Loc.Offset(1)
    Next Loc
    
End With

wsData.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

2) As for the second item, this makes me ask did you really need the item above at all or was that just the stepping stone to this final process?

The reason I ask is this is very easy compared to the one above, if you don't really need the columnar lists and you just want to go from INPUT sheet to outputted text files...?

[JBeaucaire]

Since the first macro is already written, the second will work from the created sheet.

Sub TextFiles()
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      10/14/2010
'Summary:   Columns of data are saved to text files, name in cell 1, data below
Dim fPath As String:    fPath = "C:\2010\"
Dim RNG As Range:       Set RNG = ActiveSheet.Rows(1).SpecialCells(xlConstants)
Dim cell As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    For Each cell In RNG
        Range(cell.Offset(1), Cells(Rows.Count, cell.Column)).SpecialCells(xlConstants).Copy
        Sheets.Add
        Range("A1").PasteSpecial xlPasteAll
        ActiveSheet.Move
        ActiveWorkbook.SaveAs fPath & cell.Text & ".txt", xlText
        ActiveWorkbook.Close
    Next cell

Application.ScreenUpdating = True
End Sub

[abc007]

Dear Jerry,

I cannot say anything more!

THANKS A TONNE. IT WORKED LIKE MAGIC. I used to spend lot of hours sorting and then copying the data to both a new sheet and then to a text file as I requested.

I need both the steps. Thanks to the fact that you provided code for both steps.

Pavan