Macro to Query Select Fields

[Grepees]

• I have an Excel Sheet with Fields from A to Z
• I need to create a report which will be a selection of some of the Fields. This may change from moonth to month depending on what i'd like to show
• I am looking for a Macro that can to the following;
♦ Select a list of fields in a sheet eg. A, D, F, L, S T, O
♣ Export those fields into a new sheet
◘ Save the new sheet at a designated location ( i will provide the path and File name) eg. June Month end Report

Any help on this would be very much approciated. Thank you very much in advance you good people out there.

 

[Fuji]

If you have header in row1.
AdvancedFilter

Sub test()
    Dim ws As Worksheet, Cols, i&, wb As Workbook
    Cols = InputBox("Enter columns")
    If Cols = "" Then Exit Sub
    Set ws = ActiveSheet
    Set wb = Workbooks.Add(1)
    Cols = Split(Cols, ",")
    For i = 0 To UBound(Cols)
        ws.Range(Cols(i) & 1).Copy wb.Sheets(1).Cells(1, i + 1)
    Next
    ws.Range("a1").CurrentRegion.AdvancedFilter 2, , wb.Sheets(1).Range("a1").CurrentRegion
    wb.SaveAs ThisWorkbook.Path & "\abc", 51
    wb.Close
End Sub