Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Copy data on date criteria look first date in column A

    Hello All,


    i need to copy data on dates crietria suppose if in sheet 1 first date is 1/1/2010 on A2 then copy only the data of 1/1/2010 to 1/31/2010 to sheet (Result need for Sheet1). if find in first date in sheet2 is 2/2/2012 then copy the data from 2/2/2012 to 2/29/2012 same in the sheet3 if first date in A2 is 3/1/2012 then look the last date of this particular month if last date is of this month is 3/29/2012 then copy the data to sheet (Result need for Sheet3).


    The sample is attached.

    Thank you
    farrukh
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Farrukh

    To do this without VBA, just use the Data > Filter options:

    For example, on [Sheet1] put the cellpointer in cell [A1] and in the Excel2007 top-panel Ribbon select the Data tab and then select the Filter option. This will display a series of 'filter-dropdown-arrows' in the heading row.

    Next, click the filter-dropdown-arrow in cell [A1].
    From the displayed menu, select the Date Filters > option to show a new menu, and then select (at the bottom of the options list) the menu item All Dates in the Period > and then choose, for example, January.

    This will now show a filtered list of the January records.
    You can now copy all of these January records by clicking Ctrl-A (to select the current region) and then Ctrl-C to copy the January records to the clipboard. Then, move to a new sheet or existing sheet, put the cellpointer in [A1] and press [Enter] (or Ctrl-V) to paste the records.
    Repeat for other sheets.

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Respected Zeddy,

    This is nice solution , but i have a senario iam buliding the reports using excel vba + sql . The problem i have when i fix the data to template some time the equipement is closed suppose for the date of 1/20/2010. The report is for one month range always. When i give the static range means 2 to 32 row it gets the data of 2/1/2010 because of closing of equipement. for this i think of code looks at A2 cell and then append the data in sheets (result needs) from there i ll pick the columns and cells and fix in the template. sorry if i cannot explain well.

    Cheers
    farrukh
    Last edited by farrukh; 2012-02-24 at 07:59. Reason: Change range 1 to 31 to 2 to 32

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Farrukh

    The attached file uses VBA to do what you want.
    As before, I have added a [Parameters] sheet to 'store' the date ranges required for the filter operation.
    I have named the criteria ranges as crit1, crit2 and crit3.
    I have used formulas within the criteria ranges to define the From and To dates for the filter.
    I have changed the sheet names for input and output so that the VBA code is easy to follow.
    I have assigned the VBA code to buttons on the [Parameters] sheet.

    You should be able to amend and modify this to suit your purpose.

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2012-02-25)

  6. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Thumbs up

    Respected Zeddy,

    Thank you exactly this resolved the issue ...

    Best wishes

    Farrukh

  7. #6
    New Lounger
    Join Date
    Feb 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    Very educational. Thank you. Able to finish my project now.

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Ketan

    Welcome to the lounge.
    If you get stuck on Excel I find this site a brilliant place to ask a question.
    I have been helped many times here and there are lots of people that are very happy to share their expertise.

    zeddy

Posting Permissions

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