Copy and consolidate from multiple sheets

I have a sheet with serial numbers and part costs. We will call it sheet 1
Another sheet with serial numbers and labor costs. We will call it sheet 2
I would like to have all parts for an individual serial number added together and put in a row in sheet 3 with the total part cost for that serial number. Also, I would like to use sheet 2 to add up all labor costs for a serial number and have that number in the same row as the part cost for that serial number. This would give me on sheet 3:
A B C D
Serial total part cost total labor cost total cost

There are also several other columns I would like to copy over onto sheet 3(account name, city, st, region...), but I can figure those out if I could get these transferred. I have tried a pivot table, and I can use that for each individual sheet to get the individual part costs summed into one row for each instrument, and another one to sum labor cost for each instrument, but I cannot get these combined into one sheet as sheet 1 and sheet 2 do not have the same data, other than serial number.
Thanks

attached is a sample of what I am looking for. there are many more columns in both sheet 1 and sheet 2 and 60000+ rows in each. I would like to get all needed data into one sheet so I can make a pivot table from that one sheet with all the data I am looking for.

Yes and no. The data is all there correctly, but I do not think it would work for my sheet. First off there will be like 15000 unique serial numbers and sheet 3 would need to pull these automatically from sheets one and two and consolidate to one row. This data will be taken each month so it will have different number of rows for each individual call for each instrument. Also, the formula is manually selecting the region for each serial number and that will need to be automated.

Thanks for all the help. I do not know if that will not work or if I am doing something wrong. So, enough with abridged examples. Attached is a few rows from the actual sheet I will be working from and a third sheet that I am looking to create. I did the total cost sheet manually from the data supplied in the first two tabs. I have also added a row in the total cost sheet with where I got the data and what I want to do with it.
Again, this sheet shows 2 of many thousands of serials so the total cost sheet will need to do everything automatically from the data in the first two sheets. Also, the first two sheets will be updated monthly and I would like the data in the total cost sheet to reflect these updates. I used to think I know how to use excel, but this is way beyond my knowledge and all help will be greatly appreciated as I have been working on this one thing for over a week now.

Also, I am not the best with VBA code I guess because I cannot even get a basic one written to consolidate by serial number and sum costs from one sheet. If response if for a new code, please provide detailed explanation of the code if I will have to write it.
Thanks.

Try this

Please Login or Register  to view this content.

Thank you very much, I was able to use that macro with the sample data to spit out exactly what I wanted. But...... I tried to use this on the whole workbook which the tabs are named the same and the columns are named the same, just a whole lot more rows and I got a Run-time error '9': Subscript out of range.
When I debug:
a = Sheets("12 month rolling hours").Cells(1).CurrentRegion.Value
ReDim w(1 To 7): w(7) = a(i, 8)
The bold part is in yellow
I am not sure if any other parts will error out but I cannot run past there with the error. I am going to try to understand this code you wrote and see if I can see the reason for the error, but I am sure you could get an answer much quicker if you get a chance.

Thanks again, I feel like this is so close to exactly what I am looking for.

You must understand that the code was written specially for the sheet layouts that you uploaded.

If the sheet layouts differ from the sample, the code should altered accordingly.

Post your actual workbook with exactly the same format with the dummy data.

The format on the sample is exactly the same as one the master sheet, I just copied some rows from it. All columns are in the same order on both sheets. I don't think this would be the issue, but on the 12 month rolling hours, column H, the one that is being summed, has some "0"'s in there. There are no blank entries, but there are some 0's. I would attach the entire master workbook but it is way too big.

Also, again not sure if this matters, the workbook with the raw data I am using has a few other tabs with some pivot tables on them. There are 6 tabs in total on the master, only the last two are the raw data, the 12 month parts and 12 months labor.

Originally Posted by dutch1213

I tried to use this on the whole workbook which the tabs are named the same and the columns are named the same, just a whole lot more rows and I got a Run-time error '9': Subscript out of range.
When I debug:
a = Sheets("12 month rolling hours").Cells(1).CurrentRegion.Value
ReDim w(1 To 7): w(7) = a(i, 8)
The bold part is in yellow
I am not sure if any other parts will error out but I cannot run past there with the error. I am going to try to understand this code you wrote and see if I can see the reason for the error

That error means, Array "a" has smaller than 8 columns.
That tells the data range is not correct, you must have blank column(s).

Not good with VBA at all, so let me see if this makes sense to me. Array "a" based on the code is a = Sheets("12 month rolling hours").Cells(1).CurrentRegion.Value
based on the 12 month rolling hours tab, there are nine total columns. In the sample where the macro worked, there were also 9 total columns.
Or maybe this is not array "a" and you could clue me in to what I should be looking at.
Sorry for being so dumb with this and I really appreciate your patience.

I just need to see the sheet layouts.

CurrentRegion only get from A1 to any column/row wide/depth that have no blank column/row.

If a(i,8) says out of range, that means 8 (column 8) is greater than actual data area.

OK I think I get that. The sheet layout of the raw data has the same number of columns as the sample that you worked from and they are all named the same as well. The difference is the rows and the amount of regions. If the 12 month rolling hours, there are 13 different regions that could be there, not just the two used in the example. Also, the other difference is the rows. In the sample, I just used two different serial numbers with 18 rows of data before the blank row where the full sheet has 60000+ rows of data before the blank row.

Just run this code and read the message.

Please Login or Register  to view this content.

$a$1:$i$63433

The I have no idea why such error raised.

I am going to grab some different data from the same source with more regions and more rows and see if the script will work on that. Again, the full set of data has more than 2 different instruments and two different regions and two different locations, etc. There are over 1000 unique serial numbers, each with its own data. Some serial numbers with have identical account names and locations. Would that cause any issues?

I just tested the code with over 60000 dummy records and runs without error, so I can not help you more unless I see your file.

See if you can make sense of this. I took the raw data and for both worksheets did a select all and copied them into a new workbook and created a new tab named Total Costs and ran the macro without error. I am going to redownload the original raw data file and try with that. Any idea why a select all and copy would work. I named the tabs the exact same on both
You have been beyond a huge help, and I hate to ask, but when I did do the copy and ran the script with all the data, it is exactly what I asked for, but I noticed I forgot to ask one thing. the Total Costs tab column A is serial number which I definetly need, but I forgot to add a column for the type of instrument it is. This data is in column G of 12 Month Part Data and Column C in 12 month rolling hours. Would that be hard to add that column into the script that is already written?
You have already been a life-saver and I hopy you can do it again.

jindon and JBeaucaire, thank you both so very much.
Attached is an updated sample of what I am hoping for. I am still playing with jindon's code and the original data to see why it won't run. I can copy all for both worksheets and run it fine in a new workbook, just not from original raw data file. The original file has the raw data tabs and also 4 pivot table made from the data. I am trying to get the data in a seventh tab on the workbook to be what is attached.

OK,

I think your original file have problem.
If newly created file is fine, suggest to delete original for avoiding any problem.

try

Please Login or Register  to view this content.

That is exactly what I was trying to get to and could not have done it without your help. Thank you so much for that.
I can take the original data and copy it into a new sheet and run this with no issues. I am going to try to find out why I am getting that error with the original workbook. I would send you the entire workbook of original data but it is 80MB. Do you know if I could send that to you anyway? I would prefer to not put the whole thing on the forum, and not even sure if I could with a file that large.
Thanks again.

Alright, don't know what happened, but I am getting that error every time I try to run the script now. I have attached a very small example of a sheet and it is still giving the same error. If you get some time could you look at the new sample and try to run the script above and see if you can see why the error is occuring?

Still same error on my end:
a = Sheets("12 month rolling hours").Cells(1).CurrentRegion.Value
ReDim w(1 To 8): w(8) = a(i, 8)
runtime error 9 subscript out of range
This occurs when I try to run your script on just these couple lines of data.

OOOOOps...

Sorry, that was my mistake...

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

So it should look

Please Login or Register  to view this content.

That fixed the error, thank you so much. I think the output is correct, but after the last row of full data, total labor cost and total cost have a bunch of data at the end. They are just a bunch of numbers in those columns, but columns A-G have no data in them. Any thoughts on that?

No idea about what you are saying.

I can only see the result from the file you attached that has all been filled.

The ealier script that I only got to work with one set of full data, gave me 5514 unique serial numbers with sums for each.
The new working script gives me 4849 unique serial numbers with sums and 923 rows with only a labor total and and total cost

I will email you the full zipped file

Can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Those Serial No.s are not in "12 Month Part Data"

Yeah, that added the serials for all those, but no other information and some of the rows show just the labor hours such as 1.5, 4, 1, 3, 2......
It is like it did not multiply the hours by 270, but for some others it did. If there is not part data for that serial, it is fine to just have labor hours, but could it not still show region, instrument and location info?

Yeah w(8) = a(i, 8) should be w(8) = a(i, 8) * myRate

when I put that into:
ReDim w(1 To 8): w(2) = a(i, 2): w(8) = a(i, 8) * myRate
I get runtime error 13 type mismatch

OOps
Sholud be w(8) = Val(a(i, 8)) * myRate

There it is, perfect. Thank you so much.
One more thing, I should be able to figure it out with a little research, but if it is quick, how could I format the output to have columns A-F right align, columns G-I right align and formatted to currency with 2 decimal places and finally row A bold and centered?
No big deal if it is time consuming as I can do that after running the macro manually in a few seconds.

Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

jindon,
Thank you so much for all your help today. I could have never done anything even close to this without your help. Thank you so much.

No worries and I guess it is time to mark the thread as "SOLVED"