Copy Rows from selected Sheet to New Sheet based on Cell Value

**joemcmillen** · 03-27-2008, 10:42 AM

i have a workbook that has a lot of sheets but i need to pull information from the one sheet "Veneer Log" i Need it to make new sheets with the same heading as on the "Veneer Log" (Rows 1 & 2) Sheet but it needs to be filtered by the "Product" Column (H) with a new sheet made for all the diffrent products i.e. Dimensional, Drywall, Corners - Thin V., Accents,..... so each product will have a new sheet with i am hoping someone can help me with this. This log changes Daily and it would be nice to have a sheet with only the same product on it to compare new orders so we can batch run. i hope i have given you enough information so someone can help me with this. i have attached a sample log the real log has about 10 worksheet for diffrent departments but i only need info from the Veneer Log Sheet.

Thanks for everyones help i love this forum and have learned a ton from all of you here.

Thanks again,
Joe

**Charles** · 03-27-2008, 03:07 PM

Hi,

If your copying data from "Vener Log" to worksheets with the name of the item, how will you determine if the data is already on the sheet being copied to?

**joemcmillen** · 03-27-2008, 03:15 PM

it could be a macro that makes a new sheet so there is no duplicate data. the main thing is for each department to know how many orders they have and what is on each order. the products are the same for each department but the material and finish and quantity are all diffrent.

i hope this answers your question. i am a little confused buy it.

cheers

**Charles** · 03-27-2008, 03:31 PM

Hi,

The macro will loop thru the Venner sheet and copy product to the named product worksheet. After the macro is complete you add more info the the Vennes and you run the macro again it will start at row 3 and copy the info on row3 to the worksheet for that product. Which is already there from the previous run of the macro.
Need to someway of tell the macro that the product info already exist.
I'm looking at the job nr. to tell the maco not to copy because its already in the worksheet for the product.

**joemcmillen** · 03-27-2008, 04:11 PM

ok i see the question and the problem now. is it possible when the macro is run that it clears the old sheet first so there wouldn't be duplicates?

we wouldn't ever edit the cells on the extra sheets they are to print and give to the department that they are for the only changes to cells would be made in the original "Veneer Log" cells and that is printed once a week to go to the production meeting to see the over all orders we work on first in first out so we would know what is at the top of the list to get shipped.

**Charles** · 03-27-2008, 04:16 PM

Hi,

If thats the way you want to set it up. It can be done.
I'm looking at the job nr if exist do not copy.

**joemcmillen** · 03-27-2008, 04:31 PM

the only problem is if the same order (job number) had the same product but two diffrent material it would be at the same department because it is the same product but they wouldn't see the second one because the macro would screen it out because the job number already exists. this does happen. the log you see is a small portion of the complete log. the real log is about 45 pages when printed. i just wanted to show a example.

**TonyS** · 03-27-2008, 06:48 PM

Hi,
I think this should help.

Please Login or Register  to view this content.

Let me know!
Tony

**Charles** · 03-27-2008, 07:27 PM

Hi,

This is what I came up with.
Open workbook and click on the "Click" button.

**joemcmillen** · 03-28-2008, 11:11 AM

thanks for your help. i have another problem is there a way to just copy the info the cell shows and not the formula? right now each cell is a copy from a order log on a web derive so when i run the macro and it copys the rows to the new sheets one at a time and transfers then copys the next row and then transfers from the main log.

Please Login or Register  to view this content.

**Charles** · 03-28-2008, 11:28 AM

hi,

This code is with out formula.

Please Login or Register  to view this content.

**joemcmillen** · 03-31-2008, 11:48 AM

every time i run the macro. the sheet try to update from our main office over a web drive and then just keeps repeating the update. i let it run for an hour but never did it stop. so i cancel it and then i get a lot of ###### in cells and also lost info. i don't konw what to do. i can upload the real log instead of a sample but i don't konw how it will work not being able to contact the web drive to update.

joe

**joemcmillen** · 04-01-2008, 11:36 AM

here is the full log. i know there are a lot of pages. but i only need a few pages. there is a "Result" work sheet. it is the toal of each product finish and material. i need this page. it pulls info from about 20 work sheets. i don't need these worksheets if there is a diffrent way to do this formula. but this is the way i know how to do it.

and the second part i need is new sheets to be made like the "result" worksheet. but i need it to pull each row from "veneer log" based on the Product Column (I) so all orders that are "Flats - Thin V" would be a worksheet. i hope this makes it a little more clear and i hope to get this figured out.

all the info for "Veeneer Log" is pulled from a order book at our main office over a web drive. so on the additional pages i need the cell info but not the formula that tells it to pull the row from the order book. if this is possible. to keep the workbook quick and not have to connect every time i would run the new macro.

again thank you for any help. everything has been great so far i am just having problems with this.

cheers,
joe

**Charles** · 04-01-2008, 02:21 PM

Joe,

You want the Result worksheet, and the additional "Product sheets", and you do not want the formulas that are on each worksheet, but you need the formulas in the Veeneer sheet to stay? If a new worksheet is needed then you want it to look like the "Result" sheet?

**joemcmillen** · 04-01-2008, 04:31 PM

yes i want the result sheet and the additional product sheets.

the additional work sheets can stay or go just depending on how you do the result sheet. the way i have it now the additional work sheets have to be there.

the look of the result sheet is ok. i would like the first three rows of the "Veneer Log" to be at the top of the result sheet that way it would look nice. but it by far dosen't have to be that way i just need it to work. so the look does not matter at all.

the formula i am talking about that i don't need is the formula on the "Veneer Log" worksheet that is
[ ='W:\[LEAD TIMES for Veneer Stone.xls]Lead Times'!G410 ]

i don't want it to pull the line from the LEAD TIMES sheet i want it to pull the line from the "VEENEER LOG" sheet. because when it trys to pull the line from the LEAD TIME workbook it is on a web drive and then it has to download each line at a time after checking the LEAD TIMES workbook for the line. it just takes forever to do that because it is over 1MB and if you have a 100 lines to copy it transfers each line one at a time and takes foever.

does this help clarify what i need.

thank you so much for your help i am new to this VB but if you ever need AutoCAD help i am your guy.

again thanks

joe

**Charles** · 04-01-2008, 04:39 PM

Joe,

Thanks. I'm almost there.
Now I need to know about the sheet names. The last example you sent show the sheet name tabs different for the first example you showed.
I'm thinking it would be easier if we keep the sheet names as in your first example.
LMK.

**joemcmillen** · 04-01-2008, 05:20 PM

keeping the sheet name the same as the name of the product would work perfectly.

joe

**Charles** · 04-01-2008, 08:36 PM

Joe,

This is what I came up with.
Just click the "Result" button.
It should create the worksheets and do the Result sheet.
After the first run each time you click the Result button the program should
clear all worksheets and repopulate them alonh with the Result sheet.

LMK

**joemcmillen** · 04-02-2008, 10:10 AM

ok i need to apologize i have done a bad job of telling you what i need. the sheet works great. the only two problems i have are the result page is a total page. i need the total of each product, material, finish i.e. Flats - Thin V / Aux Vases / Snapped = 2000 sq ft total there might be three orders that make up that 2000 sq ft. so the results page right now is just showing all the orders one line at a time and doesn't give me a total.

the next is the new pages they work great and they are what i am looking for but i need the customer and job# for sure. i would like the rows to look like they do in the "Veneer Log" worksheet. but i need for sure the job number and customer.

again i can't thank you enough for your help i am sorry for not describing what i need.

thank you for your time i don't konw how i can repay you.

cheers,
joe

**Charles** · 04-02-2008, 11:17 AM

Joe,

On the Result sheet you want the total for each product. Do you also want the job # and cust?

**joemcmillen** · 04-02-2008, 12:33 PM

i don't want job number and customer on the "result" page it could be 3 cutomers and 4 diffrent jobs for a total. of the product, material and finish.

on the product pages i.e. "Flats - Thin V", "Slabs" or "Old World" ect.... . i want customer and job number for each line or if it can be a copy of the Veneer log and look like the Veneer Log worksheet with all that info that would be great.

joe

**joemcmillen** · 04-03-2008, 01:49 PM

is this feasible?

**Charles** · 04-03-2008, 01:51 PM

Joe,

See if this is closer to what you want. I change some of the format for ease of copy and past. I do not have the totals as of yet.
If this is close to what you want let me know. Also if you can on the result sheet provide a example of the way you want the total to show.

**joemcmillen** · 04-03-2008, 03:02 PM

you have it doing what i want it to do.

but for the product pages it is pulling the on order number "Veneer Log" Colum D and i want that number but i also need the Job Number "Veneer Log" Column B

so product pages should be

Customer (A) "Veneer Log"
Job Number (B) "Veneer Log"
Product (I) "Veneer Log"
Material (C) "Veneer Log"
Finish (H) "Veneer Log"
BLANK
BLANK
On Order (D) "Veneer Log"
Finished
BLANK
Amount Needed
Amount Complete
Complete

after this i will stop bothering you i