none
when i enter the filter range i should genearte new excel with filtered data RRS feed

  • Question

  • I have a master data excel where I have 19 columns  with 27000 rows  , I need a formula to generate new excel based on due data column 19 of master data , example if I select due date January 18, feb18 march 18 I need these data to be copied to new excel automatically ,  master data excel is password protected…

    Dept

    Desc. Code

    Inprogress

    File #

    [Style]

    [Size_Disp]

    [SAP_MatNo]

    [Manufact]

    [Man Part NO]

    [Mfg. Serial No.]

    [Cert_Date]

    [In Service]

    [Inspect_Lvl]

    [Cert_Status]

    [Date_Scrap]

    [Scrap_Reason]

    [Update_Date]

    [Update_UserID]

    [Due Date]

    Monday, July 2, 2018 5:50 AM

All replies

  • Hello DEEPU1980,

    Excel does not provide an event for filtering worksheet. As a workaround, we usually use a formula to count visible cells in one column. Once you change the filter condition, the formula will recalculate to count the visible cells. So we could try to use WorkSheet_Calculate event to catch the filtering operation.

    However, this workaround has two limits. One is that your application Calculation Option must be set as Automatic. The other is that you should make sure there is no other formulas will recalculate except filtering. 

    Please let me know if you could accept these limits so we could try to continue on this direction.

    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, July 2, 2018 9:23 AM
  • only filtering is required and when we set as automatic can we change the filter at any point of time eg this month i need it for jan feb march filter and next month  choose april may june as filter..
    Monday, July 2, 2018 11:07 AM
  • Hello Deepu1980

    >>can we change the filter at any point of time

    It will cause effect on filtering the data.

    The whole design is enter in a cell with formula "=SUBTOTAL(3,A:A)". It will count the visible cells in column A.

    And then in the sheet module, use below code.

    Private Sub Worksheet_Calculate()
    Dim WS As Worksheet
    Set WS = ThisWorkbook.Sheets("Sheet1")
    lastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row
    Dim NewWB As Workbook
    Set NewWB = Application.Workbooks.Add
    WS.Unprotect
    WS.Range("A1:S" & lastRow).Copy NewWB.Sheets(1).Range("A1")
    WS.Protect AllowFiltering:=True
    End Sub

    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.

    Tuesday, July 3, 2018 8:09 AM
  • Hello DEEPU1980,

    What's the current state of the thread? If your issue has been solved, I would suggest you mark helpful reply to close the thread or provide your solution and mark it to help more people who runs into the same issue.

    If not, please feel free to let us know if you have any updates for your issue.

    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.

    Friday, July 6, 2018 6:46 AM