Excel 2007 : Populating a worksheet with specific data from another

[lynusann]

I'm not sure the best way to ask how to do exactly what I'm trying to do, so if I don't make sense, please ask questions and I'll try to clarify.

I have an excel document that I currently use that I need to add 4 additional worksheets to. I've uploaded the document so that you can see what I'm currently working with. I need the 3rd and 5th sheet to essentially be formatted exactly like the 1st sheet. And the 4th and 6th to be formatted exactly like the 2nd sheet.

Currently, I manually enter data that's submitted to me on handwritten sheets into my excel document to match each route number for each day of the week. The columns that never change are A-E and M. Columns F-L change every week depending on the data submitted to me. At the end of each week, I upload all this information into our systems. Currently what I'm doing is copying and pasting specific rows over to another workbook and uploading it that way, which is extremely time consuming. What I would like to do is add additional worksheets that will automatically grab the data I'm currently manually copying and pasting.

So for example, I would like my worksheet titled "AM Returns" to look for anything in the worksheet titled "Returns," in Column E that starts with a 3 or 4. On the "Returns" worksheet, Column E has all my route numbers listed and those never change. So, if a route starts with a 3 or 4, I want the entire row to populate into an identical layout in worksheet 3. Similarly, I would like for the worksheet titled "PM Returns" to populate in the same format with anything starting with an 8.

What I'm hoping to do is not have to search through over 1000 lines of data every week and copy/paste all the "4" routes, and then all the "8" routes into different sheets.

Sheet 1, 2 & 3 I have to keep formatted the same because those are the sheets I use to actually do my uploads and my system won't take the data in any other format. Those pull from the other Returns worksheets.

Hopefully what I'm asking makes sense. I'm not the most highly skilled in excel, but I'm trying to make my day slightly more efficient, so thank you in advance for any help!

[carsto]

You could get a fully automated method using VBA but since you stated you are not highly skilled, I'd hate to give you something that might fail later rendering the system useless.

Copy the entire worksheet by right clicking on the tab. Then rename the new sheet to AM Returns

Are you familiar with AutoFilter?
(I can't tell you exactly how to do it in 2007 because I'm on a computer running 2000 - sorry)

Select your Row 12 and apply an autofilter (Data > Filter > AutoFilter)
Then using the drop down menu on E12, select Custom
set your criteria to <300000 OR <=500000 this will filter down to everything NOT starting with 3 or 4
then highlight the visible rows and delete
It will only delete the visible rows so when you unfilter, the rows starting with 3 or 4 will be left

To obtain those starting with 8 do the same except
copy & rename to PM Returns
set your criteria to <800000 OR <=900000 this will filter down to everything NOT starting with 8

[lynusann]

Thank you for the reply! So that's actually what I had done earlier today, but unfortunately it leaves me with huge gaps on the secondary sheet that follows each returns sheet. I should have worded my first post a little better in saying that I'm relatively proficient at excel, but nowhere near the level of many people on here (hence reaching out to this community).

Myself and another colleague were the ones that put the majority of the attached workbook together so we can get around and understand things at about an intermediate level. Now, with that said, I've had limited exposure to macros but it's something I could likely pick up quickly. If you have any recommendations on where I could start that would be great. In reality, I have about an additional 1000+ lines of data that I deleted off my original since I couldn't upload a larger file to this site, and with this much manual data entry, I am willing to spend whatever amount of time necessary to save myself headache in the future.