+ Reply to Thread
Results 1 to 25 of 25

Copy data using start and end date to another sheet

  1. #1
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Copy data using start and end date to another sheet

    I have a user form, in which user will select start and end date, it will generate reports using these dates.
    A report to be generated from Sheet named "BD Raw2" using start and end date. The code should look into sheet BD Raw2 data and copy only those data which is in between start and end dates defined by user using calendar in the form to Sheet named "Report". I have written the code but it has following issues:
    1) it copies data randomly and does not look into start and end date(i think)
    2) data is randomly pasted in sheet "Report".( It should properly look for last blank row and paste the data there)
    3) It should copy only values not formulas

    Sheet is attached

    Code is:
    Please Login or Register  to view this content.
    Your kind help will highly be appreciated
    Attached Files Attached Files
    Last edited by Zahid0111; 04-05-2020 at 02:16 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    353

    Re: Copy data using start and end date to another sheet

    It's difficult to check because there is no calendar in Form.
    Attach a valid attachment.
    Best Regards,
    Maras.

  3. #3
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    @maras_mak ...as you can see in the attach pictures, it is there...
    if you can,t see it then consider the text boxes as input for date because microsoft calendar control 11.0 may not be installed at your end...
    I am just taking dates from the calender..if they don,t appears to you, kindly manually input dates in the text boxes
    Attached Images Attached Images
    Last edited by Zahid0111; 04-05-2020 at 03:09 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    353

    Re: Copy data using start and end date to another sheet

    This is a misunderstanding. I want to check how selected dates are represented in your code.
    To check this, a workbook must be attached, not png.

  5. #5
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    @maras_mak i have already attached the workbook...but again i m attaching the workbook..
    Plus i m sending two files embedded in the attached excel file as new folder2, which will enable you to open calendar in worksheet. As i think you don,t have these stuff installed due to which you are unable to see the calendar form..
    kindly put these two files at below location:
    (for 32bit windows) C:\Windows\System32
    (for 64bit windows) C:\Windows\SysWOW64
    2) then run Command prompt
    3a) enter this command for 64 bit OS- (cd \windows\syswow64)
    3b) enter this command for 32 bit OS- (cd \windows\system32)
    4) enter this command (regsvr32 mscal.ocx)
    5) enter this command (regsvr32 MSCOMCT2.OCX)

    Done, now launch this excel file, you will be able to see calendar form...
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-24-2009
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: Copy data using start and end date to another sheet

    Please Login or Register  to view this content.
    Didn't check but you can try.

  7. #7
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    @zameel thanks for reply but same issues still persists. like shown in snapshotss below. it copies data to where ever it wants, it must paste the data in last blank row. also the dates which i selected are in snapshot, it should only copy data for march.
    Attached Images Attached Images

  8. #8
    Registered User
    Join Date
    06-24-2009
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: Copy data using start and end date to another sheet

    Try formatting the two textbox to date format as i see in the picture the date picker and textbox are different.

  9. #9
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    Already did this..but in vain...

  10. #10
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    353

    Re: Copy data using start and end date to another sheet

    @ Zahid0111, thanks for the attachment. I have to install.
    Try this code.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    Hi maras_mak...Welcome and Thank you for the code...i was waiting for your kind reply all the DAY..
    Well it,s working and copying but one issue still persists...as you can see in attached pictures i have selected only 1st march to 31st march but it copies for JAN, FAB and MARCG...can you please check it
    Attached Images Attached Images

  12. #12
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    353

    Re: Copy data using start and end date to another sheet

    This is a problem with system dates. I have another date format "dd-mm-yyyy", yours is "mm / dd / yyyy".
    Try the code below.
    Please Login or Register  to view this content.
    Last edited by maras_mak; 04-06-2020 at 09:23 PM.

  13. #13
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    Wow now this works great..Thank you so much dear..

  14. #14
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    353

    Re: Copy data using start and end date to another sheet

    Glad to help you.

  15. #15
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    @maras_mak ..sorry to bother you one more time please need your help:
    I have inserted another portion to the form, user will select "Mechanical" or "Electrical" from drop down list and then insert dates. Now this time when he press Generate button it should look for Mechanical or Electrical in Sheet "BD Raw2" at column G and copies all the data to Sheet "Reports Mech-Elec" for item selected at drop down list.
    Please help me in this..thanks in advance..
    Attached Images Attached Images
    Attached Files Attached Files

  16. #16
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    353

    Re: Copy data using start and end date to another sheet

    Before using "CommandButton6_Click ()" you must change the names to mine (in code) or your own
    for textboxes:
    Startdate -> tbStDate_me
    EndDate -> tbEndDate_me
    for Combobox -> cmbType
    It should work fine.
    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    Hi Thank you maras_mak ...it is working for Mechanical but not for Electrical. I have input the dates for electrical from drop down but when i clicked generate it give me the attached message. You can see in the other attached picture electrical is in the list.
    Attached Images Attached Images

  18. #18
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    353

    Re: Copy data using start and end date to another sheet

    Zahid0111, my friend, look carefully at the "BD Raw2" sheet. There are 'Electrical' there, but they are out of the date range you selected in Userform !!!
    This is just info that there is no data in this date range.
    It works well!

  19. #19
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    Oh my bad...Really sorry...i didn't look into that..Thank you so much..indeed it works fantastic..
    If i want to generate report in new excel file, is it possible please? as currently we are generating(copying) them in our main workbook sheet "Reports" or "Reports Mech-Elec". if i want to copy/generate in new workbook?
    OR if we can copy Mechanical to "Reports Mechanical" Sheet and Electrical to "Reports Electrical".
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Zahid0111; 04-08-2020 at 05:04 AM.

  20. #20
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    @maras_mak brother need your kind support please help

  21. #21
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    353

    Re: Copy data using start and end date to another sheet

    I'm sorry, I also have my job.
    Create two workbooks named Reports Mechanical.xlsx and Reports Electrical.xlsx. Each of them has 1 sheet called "Reports".
    You must enter your own path to the workbooks in the code.
    The code does not save changes and does not close reports.
    I think it should work well.
    Please Login or Register  to view this content.
    Last edited by maras_mak; 04-09-2020 at 06:15 PM.

  22. #22
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    Thank you So Much Maras..Really appreciate your kind time and work...and sorry for bothering you..
    I have followed your instructions and this works great...
    Actually i wanted to copy Mechanical and Electrical in same sheet as i have created these sheets in the same workbook. Can you please amend the code..as shown in picture
    Attached Images Attached Images

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,087

    Re: Copy data using start and end date to another sheet

    This thread is marked as SOLVED - please remove the solved tag if it is not.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  24. #24
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    353

    Re: Copy data using start and end date to another sheet

    I think you would do it yourself. I have left comments on the code lines. You can delete them.
    This is my last help in this thread.
    Please Login or Register  to view this content.

  25. #25
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    195

    Re: Copy data using start and end date to another sheet

    Thank you Maras...Great..This works as it needs to be...This is fantastic...I m so grateful for your time and hard work...

+ 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. User enter start & end date - print sheet with start date, update 1, print sheet
    By Demanda in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2019, 07:58 PM
  2. Replies: 1
    Last Post: 02-09-2017, 05:36 AM
  3. Replies: 1
    Last Post: 02-09-2017, 05:30 AM
  4. Replies: 3
    Last Post: 09-03-2014, 09:59 AM
  5. Copy column variable number of times between start date and end date
    By gbisk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2013, 07:57 AM
  6. Replies: 0
    Last Post: 07-03-2012, 03:52 PM
  7. start a copy of data at a line depending on date
    By news in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-05-2006, 06:55 PM

Tags for this Thread

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