• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Report Creation from a Date Range [SOLVED]

D

dev.sutradhar1987

Member
I have a Sheet where the Dates are in Column "C" of sheet "Collated".I am trying to extract a Report on a Different Sheet named "Process View" where if i give in the date Range it will find the date in the "Collated" Sheet and copy the netire row and paste it in Sheet "Process View"for range "A12".

The FromDate is in "Cell C7" And ToDate is in "Cell E7").


I need a macro which i can attach to a button to get the above job done.


Please help.
 
Deb


Have a look at

http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
Hi Hui, I looked at it and understood it but my problem is i need a to get the data with a Date Range.


Suppose i enter FromDate - 15.07.2013 & ToDate - 19.07.2013. I want all the data within that date range to be populated.


With a particular "Text" i can also get the data populated using Advance Filter Macro but the with a Date Range i am unable to get what i need thats why i came up ti this forum.
 
Hi Dev.sutradhar1987


Lets make some assumptions about the shape of your data and go from there. I have assumed that your worksheet objects are sheet1 and Sheet2. I have assumed sheet1 is your Collated sheet where all your data is stored and sheet2 is the sheet (ProcessView) where your dates and summary is to appear. I will also assume you want to clear your data before you kick off the process and I have assumed the data goes from Cols A to K. Put a button on your process sheet and attach this code to it.


Code: 
Option Explicit

Sub FilterBetweenDates()
    Dim lDate As Long
    Dim lDate1 As Long

    lDate = Sheet2.[C7]
    lDate1 = Sheet2.[E7]

    Range("A12",Range("K65536").End(xlUp)).ClearContents

    Sheet1.Range("C1",  Sheet1.Range("C65536")).AutoFilter 1, ">=" & lDate, xlAnd, "<=" & lDate1

    Sheet1.Range("A2", Sheet1.Range("K65536").End(xlUp)).Copy [A12]
End Sub

I have tested this and it goes well so if the above does not work for you create a new workbook. Call sheet1 Collated call sheet2 ProcessView put some dummy data in the Collated Sheet starting in Row 2 (row 1 has headers) now ensure Col C has some dates in it and also and very importantly insure that your date range is contained within your collated sheet as the above code has no muppet proofing in it. Put two dates in C7 and E7 respectively of your ProcessView sheet and this should get you over the line.


Hope this helps.


Take care


Smallman
 
You must log in or register to reply here.
Back
Top