none
Filtering some data to get subtotals only into a new sheet RRS feed

  • Question

  • I tried to filter out some date from X, Y and Z columns,

    and written some code like below

    Sub Filter_Statement()

    On Error GoTo Filter_Statement_Error
    Dim ws As Worksheet
    Set ws = Sheet6
    Application.ScreenUpdating = False
    If ws.range("G5").Value = "" Or ws.range("H5").Value = "" Then
    MsgBox "Please fill in all of the needed information. Start Date / End Date"
    Exit Sub
    End If
    ws.Select
    ws.range("Statement1").RemoveSubtotal

    Set area2 = Sheet5.range("SalesStockOut")
    area2.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=ws.range("R4:T5"), CopyToRange:=ws.range("E15:I15"), Unique:=False
    UpdateForStatement

    If ws.range("E16").Value = "" Then
    MsgBox "No available data"
    Exit Sub
    End If
    Application.ScreenUpdating = True
    Groupit

    On Error GoTo 0
    Exit Sub

    Filter_Statement_Error:
    MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure Filter_Statement of Module Filters"
    End Sub

    Sub Groupit()
    Sheet6.Select
    Sheet6.range("Statement1").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 5), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    End Sub

    Sub UnGroupit()
    Sheet6.range("Statement1").RemoveSubtotal
    End Sub

    Sub Levels2()
    Sheet6.Outline.ShowLevels RowLevels:=2
    End Sub

    Sub Levels3()
    Sheet6.Outline.ShowLevels RowLevels:=3
    End Sub

    Sub ClearStatement()
    Sheet6.range("E5:H5,E16:I1048576").Value = ""
    End Sub

    Sub UpdateForStatement()
    'set named ranges
    On Error GoTo UpdateForStatement_Error
    'hold in memory
    Application.ScreenUpdating = False
    Set ws = Sheet6
    'reset the named ranges
    ws.Activate
    With ws
    .range("E16:I" & Cells(Rows.Count, "I").End(xlUp).Row).Name = "UpdateStatement"
    .range("UpdateStatement").Sort Key1:=Sheet6.range("E16"), Order1:=xlAscending, Header:=xlGuess
    End With
    'if error occurs
    On Error GoTo 0
    Exit Sub
    UpdateForStatement_Error:
    MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure CustomerSort of Module Add_Customer"
    End Sub

    Formulas:

    Statement1=OFFSET(Statement!$E$15,,,COUNTA(Statement!$E$15:$E$11523),5)

    So, in this what exactly I am facing the issues are

    1. The filtered data should move to another sheet and create a new csv or excel file or pdf file,

    2. Trying to send that sheet to an eMail and some message to phones

    3. Apart from this i have some data in A1:D100, in column A from A1:A5, A6:A20 and A21:A100 unique numbers, I want to retrieve the data A1:D5, A6:D20 and A21:D100 respectively. If the value in A1:A5 is commonly numeric 20(say), and to retrieve with reference of G3 I given numeric 20, the data A1:D5 should be pasted into G1:J5(say).

    Thanks in advance.

    Saturday, June 23, 2018 2:51 PM

Answers

  • Hello HarshaVardhan Vinjamuri,

    >>The filtered data should move to another sheet and create a new csv or excel file or pdf file

    We could use WorkSheet.SaveAs to save the worksheet as a new csv or excel file.

    For saving as PDF file, we could use WorkSheet.ExportAsFixedFormat.

    Here is the example code.

    Sub Test()
    'csv
    Sheet1.SaveAs "C:\Users\terryx\Desktop\TestFolder\2.csv", xlCSV
    'workbook
    Sheet1.SaveAs "C:\Users\terryx\Desktop\TestFolder\2.xlsx", xlWorkbookDefault
    'pdf
    Sheet1.ExportAsFixedFormat xlTypePDF, "C:\Users\terryx\Desktop\TestFolder\2.pdf"
    End Sub
    

    >>Trying to send that sheet to an eMail and some message to phones

    We suggest you post one issue on one thread. For the issue sending mail from excel, please try to refer to below thread. Please post a new thread if you still need more information.

    OfficeTalk: Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook (Part 1 of 2)

    OfficeTalk: Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook (Part 2 of 2)

    >Apart from this i have some data in A1:D100, in column A from A1:A5, A6:A20 and A21:A100 unique numbers,

    Please post a new thread for the issue and I would suggest you share a simple document so it will be easy to understand your requirement.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 25, 2018 2:38 AM