Select rows based on date range and copy to multiple tabs

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.

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.

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.

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.

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.