+ Reply to Thread
Results 1 to 28 of 28

Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Hello,
    Need to copy rows of data based on a range of dates from multiple work sheets (Name1,Name2,Name3) and looking at dates on each row in column “M” to a predefined work sheet (Budget). Using either a calendar form or message box initiated from a button on the predefined work sheet “Budget”. The data on work sheet “budget” will be overwritten each time excluding the top row, as this will be a header. Normally I can cannibalize found code enough to make it work, but have no idea on this one.

    To further complicate things the first two sheets (Dropdown,Insert) are normally hidden, work sheet (Name1,Name2,Name3) and the work book itself is protected and shared.

    Hope someone can help, Thanks
    Attached Files Attached Files
    Last edited by Isacc; 01-31-2013 at 01:34 PM.

  2. #2
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    I inserted a calendar from another file I have into the attached example, but maybe a message box would be better?

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    So what should be the condition for the data to be transferred?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Condition should be a date range by user input option, either by message box or calendar form.

  5. #5
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Variable is column “M” on defined sheets (Name1,Name2 and Name3)

  6. #6
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    After many Google searches for help with my question. I’ve decide a Userform would be best, I created this (in attachment Book2 of my original post) but I still need help with code to make it work. Any one?

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Isaac, i will look into this first thing tomorrow.

  8. #8
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Thank you Arlu

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Quick question - how do you trigger the form to open? Are you using any shortcut key combination?

  10. #10
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    The form opens using a button on sheet "Budget"

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    One important question - If there is data already in the budget sheet, do you want it to be cleared before running the macro?

  12. #12
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Excluding the header, yes.

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Double-click on the first button on your form and put this code in the code block that appears -
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Arlu, excellent and thank you.. it works perfect in my test book, I’m just trying to figure out why it isn’t working in my actual file.

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    As long as your format is the same as your test file, it should work. Are you getting any error.

  16. #16
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    no, no errors, its just not getting the data in my master file. Its almost as if it doesnt like the old data (dates). i'm building a new test file and making sure its not a format issue.

  17. #17
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Good morning : )
    I built a new test file (Book3), with original data from my other file. the code works but not correctly and i can’t figure out why. When I run the code using the date range (1/1/2012 and 12/31/2012) it copies over every row regardless if it’s in the year 2012 or not. When I use the date range (1/1/2012 and 1/1/2013) it doesn’t copy anything. Any thoughts?
    Attached Files Attached Files

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    It partially works and i know the issue lies with the date formats. Will try and work something out to get this sorted.

  19. #19
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Thank you Arlu... I tried messing with the date in the code without any success

  20. #20
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Does this help:

    Please Login or Register  to view this content.
    I'm entering days as d/m/yy and it works. Try other formats.
    Last edited by zbor; 02-05-2013 at 10:20 AM.

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Thank you Zbor.

    I put in data in the dd/mm/yyyy format into the form and it works correctly. If i put mm/dd/yyyy, it doesnt.

    Also Isaac,

    You should ensure that both the text boxes have dates in them. So you can put in some additional controls if the user leaves one msgbox empty. If both the fields are not filled, the macro will give an error.

  22. #22
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Thank you also Zbor. I’ve tested and it works no matter if I enter the mm or the dd first, and either using yyyy or yy… fantastic

    Arlu,
    A big thank you for all your help also. And, that is a good suggestion I do need something if the user leaves out one of the inputs or enters a wrong date such as entering to many days for a particular day of the month.

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Regarding blank text boxes, you can use this as a check point first -

    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Added to the code and it works great, thanks for the extra suggestion
    If the board would let me, I would add to your reputation back to back

  25. #25
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Thank you for the rep.

    You can now mark this thread as solved.

  26. #26
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    pardon me if i'm wrong. not good with codes. should zbor's code have an INT to it so that an entry of 21st Dec 2012 12:45 pm be also taken in if user inputs:
    20 Dec 2012
    to
    21 Dec 2012
    so:
    Please Login or Register  to view this content.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  27. #27
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    If someone needed to specify time as well as date?, in my case time wasn’t necessary.

  28. #28
    Registered User
    Join Date
    11-28-2012
    Location
    Globe, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Copy Rows To Predefined Sheet Based on Date Range Using Form or Message Box

    Benishiryo,
    I used your example and tested my file, it did not bring across both dates... you are correct. Thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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