+ Reply to Thread
Results 1 to 10 of 10

Select rows based on date range and copy to multiple tabs

  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Select rows based on date range and copy to multiple tabs

    First post.

    ProjectionExample.xls . ProjectionExample.xlsm 2 sample copies (not sure which one works)

    I am an intern responsible for sorting information based on promise dates into separate tabs for a weekly report. We have 6 teams (six different spreadsheets) each has 18 worksheets (one tab for late, one for each week -16 weeks out, and one for anything beyond that). There are between 200 and 2000 items (rows) per team.

    I am trying to build a macro that will select rows based on the date in the promise_date column and paste that selection to the appropriate worksheet (Tab?).
    I've built a mock-up of the spreadsheet I am using.
    I work for a company that deals with the government, so I'm not allowed to provide any Real Data.
    Additionally, I have excel 2007 at work and 2010 at home, so my macros don't work very well when I try to build them off the clock.
    I'm building one macro for each tab and then a "Lord of the Macros" - (Read in Gandalfs voice - "One macro to rule them all").
    Specifically, I think I'm stuck on moving from one row to the next.
    This macro will select one row based on the criteria, but then it stops.
    The message box just lets me know that I haven't broken something else (it's not really necessary).
    And, Yes, I have to do it this way. The guy I replaced built this and there are alot of other moving pieces.
    No, I can't use the data filters. I already do that (and use weeknum function too) and it still takes 3-4 hours.


    Please Login or Register  to view this content.
    Any and all help is appreciated.
    Last edited by B-Rell; 07-13-2012 at 12:14 AM. Reason: added code tags

  2. #2
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Making Progress - Re: Select rows based on date range and copy to multiple tabs

    Okay, I'm making progress.
    I think I have narrowed down my problem to the underlined line of code.
    There may be other bugs, but I am currently stuck on that particular line.

    Please Login or Register  to view this content.
    Last edited by B-Rell; 07-13-2012 at 12:13 AM. Reason: added code tags per FortySixAndTwo's recommendation

  3. #3
    Registered User
    Join Date
    06-19-2012
    Location
    Canada Eh
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    40

    Re: Select rows based on date range and copy to multiple tabs

    I think you are supposed to use code tags around your code when posting here. If you don't, the moderators are going to get you!
    EF killed Mordred

    46 & 2 is just ahead of me!

  4. #4
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    The Loop selects the correct rows, how do I get it to paste to another worksheet?

    Still working on this code. I have got the loop to work. It now stops on the correct final row (instead of on the first).

    Here's my Question?
    How do I get it to copy the selected row and paste it to another worksheet before it skips to the next line?

    Originally, I wanted it to select all of the qualifying rows and then I would copy and paste the selection once. I don't know if it's easier to copy and paste each row as it loops. (The line by line method seems like an easier solution once I saw it).

    For anybody who's following this thread, this is the first macro that I have tried to write instead of just record. (In case you're wondering why I'm stumbling through this process).

    So here's the code as it stands:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Select rows based on date range and copy to multiple tabs

    So let me try and understand your question -

    You have one sheet with data which should go into the other sheets. What is the condition for the rows to be copied to the other sheets? Just by the date?

    I understand that you want to clean up the sheets before you transfer the data and this can be done.

    Why do you want to have one macro per sheet? You just need 1 macro for the whole file.
    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]

  6. #6
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Select rows based on date range and copy to multiple tabs

    arlu1201,

    Thank you for your response. I was starting to wonder if I was alone.

    I am trying to copy rows based on the promise dates. Each numbered worksheet is supposed to have just the items that have been promised for delivery that week (each number represents how many weeks away the due date is). Currently I am using an adjustment column (promise date +2) to get a week number that corresponds to my correct week range. then I autofilter based on the week number & year and copy & paste everything to the appropriate worksheet. Another worksheet (that I didn't include in my sample) pulls data from the week number worksheets and builds a summary for supervisors (this worksheet works fine and is used for estimating materials and labor).

    I did realize that a macro didn't need the weeknumber (as that would just add extra steps). I'm just struggling to pull the rows with appropriate promise dates over to the tab that shows how many weeks out they are due. I think that I need a second loop but the logic keeps evading me. One for the "Data" worksheet to search and one for the numbered worksheet to paste.

    As to your question about one macro per sheet. I was trying to build one macro per sheet to simplify the logic for myself (as a VBA noob). I'm sure there is a better way. It's pretty hard to figure it out with just "Excel 2007 VBA for Dummies" by John Walkenbach and digging through forum posts. I hope this helps to give a clearer picture of what I'm attempting to accomplish.

  7. #7
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Almost there - Re: Select rows based on date range and copy to multiple tabs

    Here's my code as of Friday
    The code was almost working. Unfortunately, instead of pasting the qualifying rows, it was just pasting whatever happened to be selected from the data tab in all columns for every cell in rows 1 to 500.

    So I added the Offset line. Now I have a broken offset. It keeps giving the error that there is an = expected. grrr.
    Once I can get one tab to populate, I should be able to set up additional loops to populate every tab.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    My Solution - Re: Select rows based on date range and copy to multiple tabs

    I have solved the major parts of this problem on my own.
    I broke up the problem into three separate macros.

    Macro1: Clears all data tabs
    Please Login or Register  to view this content.
    Macro2: assigns the tab number
    Please Login or Register  to view this content.
    Macro3: Sorts data to respective tabs
    Please Login or Register  to view this content.
    There might be an easier way to do this (simpler or cleaner code), but this is how I got it done.
    I wanted to post a solution incase somebody runs across this post in the future.

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Select rows based on date range and copy to multiple tabs

    Regarding macro 1, are all sheets to be cleared of their contents? Are there any sheets to be excluded?

  10. #10
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Select rows based on date range and copy to multiple tabs

    Arlette,

    here are the steps I use to prepare the data.
    1. I pull up the completed form from last week - for convenience I'll call it the "projections report".
    2. I run the macro to clear all data from the data tab, late tab, MO tab, and all numbered tabs. The macro wipes them clean and sets the active cell in each worksheet to "A1".
    3. I run a report in software called crystal reports (CR) and then export the data to excel format.
    4. I copy and paste the data from the CR export to the projections report data worksheet.
    5. I run the second macro to determine which tab the data should go on. (I was manually copying and pasting until I wrote macro 3).
    6. Finally, I run the third macro to populate all of the other empty worksheets.

    There are other worksheets that I don't manipulate. These "other" tabs just pull various data out and help managment determine current & future production schedules (labor and materials). When I started, there were no macros and every step was done manually. To sort the dates, my predecessor would use the select filter to specify a date range then copy and paste each tab individually. My current solution has cut the amount of time weekly from roughly 4 hours to under 1 hour. This report is updated every Friday. My current solution (ugly but works) has freed up my day so that I'm not struggling to finish before my scheduled shift is over.

    Two observations:
    First, occasionally the clear data macro has to be run twice (depending on which worksheet is active).
    Second, the third macro (to cut and paste data) is dependent upon having the "DATA" worksheet active to run correctly.

+ 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