Macro To Fetch Data based on 2 Criteria

  • Hi All,


    Attached is my .xls with dummy data forum.ozgrid.com/index.php?attachment/57013/.


    My intention is that once the Director and Month in tab 'By Director & Month' is chosen and the Fetch Data commandbutton is clicked, a new workbook should open.


    This new work book should have :


    • all entries in tab 'Monthly Invoice Input' for that Director-Month combination,
    • along with a sum total of column Q(Total invoice amount) in the last row.



    Is this possible?


    Thanks.

  • Re: Macro To Fetch Data based on 2 Criteria


    Very rough - so it leaves you with a little work to do...

  • Re: Macro To Fetch Data based on 2 Criteria


    Thanks cytop!


    I think I have grasped the logic of the code


    Upon testing it line by line, I found that the below code is filtering out everything when it applies the criteria:


    Code
    .ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=strMth


    Hence the resultant output is a blank sheet with header.


    I thought maybe this is because the worksheet object is missing in the below code :


    Code
    strMth = Range("I7").Value 
        strDir = Range("I8").Value


    But maybe I am wrong as that did not work either.


    Request your inputs.


    Thanks.

  • Re: Macro To Fetch Data based on 2 Criteria


    Looking at your example there is no point in having unused rows within the Table


    I would use Advanced Filter, see the attached example

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!