+ Reply to Thread
Results 1 to 8 of 8

Extract the data to another sheet based on variable conditions

  1. #1
    Forum Contributor
    Join Date
    12-23-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2013
    Posts
    331

    Extract the data to another sheet based on variable conditions

    Hello,

    I have some problem with the extraction, I would like to extract the data to another sheet based on variable conditions but I can not do it. My code in just case working if the all conditions are specified. The target is that if want the code could extract the data based on one condition even, or two or three or more.

    Please, could someone help me in this problem.
    The sample file with the actual code has been attached.


    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,477

    Re: Extract the data to another sheet based on variable conditions

    I've added a new macro to extract values from "Data" sheet to "Report" sheet. To test run macro "SetFilter". Macro starts by clearing old data from "Result" sheet.

    Macro uses input from range C6:C13 on "Report" sheet to set up autofilter options. As I use a formula to copy these values to sheet "Data" a blank value in range
    C6:C13 will generate a 0 in range P2:W2 on the "Data" sheet and these the macro will ignore.

    I've set up 3 possibilities for the date. Only a start date, only a stop date and interval with a start and a stop date this will depend if you put a value in only C6 or C7. A date in both cells will set a start and a stop date.

    The autofiltered result is copied to sheet "Result" and autofilter settings are removed from sheet "Data".

    Problem with macro and using "CurrentRegion" when copy from filtered range. Have uploaded a modded file.

    Alf
    Attached Files Attached Files
    Last edited by Alf; 03-25-2014 at 02:29 PM. Reason: problem with macro have fixed it.

  3. #3
    Forum Contributor
    Join Date
    12-23-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2013
    Posts
    331

    Re: Extract the data to another sheet based on variable conditions

    Hello Alf,

    Thank you for the reply!
    I tested your code, doesn't work for me as I expect. Not able to extract data based on the filter conditions. Extract the whole bunch of data.

    I did not change anything in the file.

  4. #4
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,477

    Re: Extract the data to another sheet based on variable conditions

    Yes my bad! Change the line

    Please Login or Register  to view this content.
    with the following lines

    Please Login or Register  to view this content.
    It seems the command "CurrentRegion" ignores a filtered range it just copies all.

    Alf

  5. #5
    Forum Contributor
    Join Date
    12-23-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2013
    Posts
    331

    Re: Extract the data to another sheet based on variable conditions

    Maybe the problem is on my side, but this is what I see in your uploaded file:

    HTML Code: 
    Option Explicit
    
    Sub SetFilter()
    Dim i As Integer
    
    Sheets("Report").Activate
    
    i = Range("B" & Rows.Count).End(xlUp).Row
    
    If i < 15 Then
    
    Else
        Range("B15:M" & i).ClearContents
    End If
    
    Sheets("Data").Activate
    
    Range("Table1[#Headers]").AutoFilter
    
    If Range("P2") <> 0 Then
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">=" & Cells(2, "P")
    End If
    
    If Range("Q2") <> 0 Then
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="<=" & Cells(2, "Q")
    End If
    
    If Range("P2") <> 0 And Range("Q2") <> 0 Then
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">=" & Cells(2, "P"), _
         Operator:=xlAnd, Criteria2:="<=" & Cells(2, "Q")
    End If
    
    If Range("R2") <> 0 Then
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:=Cells(2, "R")
    End If
    
    If Range("S2") <> 0 Then
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=4, Criteria1:=Cells(2, "S")
    End If
    
    If Range("T2") <> 0 Then
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:=Cells(2, "T")
    End If
    
    If Range("U2") <> 0 Then
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:=Cells(2, "U")
    End If
    
    If Range("V2") <> 0 Then
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, Criteria1:=Cells(2, "V")
    End If
    
    If Range("W2") <> 0 Then
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9, Criteria1:=Cells(2, "w")
    End If
    
    With ActiveSheet
        .AutoFilter.Range.Copy
    End With
    
    Sheets("Report").Range("B15").PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
    
    Range("Table1[#Headers]").AutoFilter
    
    Sheets("Report").Activate
    
    End Sub
    The
    HTML Code: 
    With ActiveSheet
        .AutoFilter.Range.Copy
    End With
    already integrated in the code and doesn't do that what I would like.

  6. #6
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,477

    Re: Extract the data to another sheet based on variable conditions

    integrated in the code and doesn't do that what I would like.
    The problem seems to be that your data is set up as a table. As soon as I converted it back to a "normal" data range my macro works without any problem.

    Have a look at file "sample_modded3.xlsm".

    If you would like to keep the data as a "Table" I guess macro could be modfied to remove "Table" settings copy relevant data and then transfer data back to table formate.

    Alf
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-23-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2013
    Posts
    331

    Re: Extract the data to another sheet based on variable conditions

    now it is clear, thank you Alf

  8. #8
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,477

    Re: Extract the data to another sheet based on variable conditions

    You are welcome.

    Thanks for feedback and rep.

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract Data based on conditions on multiple columns
    By checkoncomp in forum Excel General
    Replies: 6
    Last Post: 07-03-2013, 01:54 PM
  2. Replies: 4
    Last Post: 03-05-2013, 01:02 PM
  3. Replies: 3
    Last Post: 08-10-2012, 10:41 AM
  4. Replies: 2
    Last Post: 06-01-2012, 09:24 AM
  5. Replies: 0
    Last Post: 07-21-2011, 05:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1