Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summarize multiple worksheets using VBA
I have a job costing spreadsheet that includes 7 worksheets from my different
vendors and a labor worksheet which includes hourly rates, overhead, etc. This spreadsheet accurately tells me the cost for a job and currently i have a "totals" worksheet which simply sums any data input into the vendor worksheets and the labor worksheet. On each of the 7 vendor worksheets, there are materials, plants, trees,etc (landscaping company). I also include as columns, our cost, 15% markup price, 20% markup price and 25% markup. My totals worksheet shows my labor costs, along with a sum of the individual vendor worksheets at all levels of pricing (ie:our cost, 15%, 20%,, etc). The trick here is that I would like to see what comprises the order from each vendor on the summary page as well whereas now I only see the dollar totals. Ideally if in vendor worksheet 1, I need 10 items and in vendor sheet 2, i need 4 items, i would like to see the 10 items and the 4 items on my totals sheet along with all of the cost/pricing amounts. I havent been able to find any built in functions that can handle this so i'd be open to trying something like that as well. ANy help is greatly appreciated...thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
summarize multiple worksheets using VBA
Tony
From what you say about what you have and what you want, I would venture a guess that it would be an easy task to write you some code to do it for you. But without a lot more information, it would be impossible to do so. Write down a procedure you would follow if you were doing what you want and you were doing it all manually. When you say you would go to sheet X and row 6 and copy cells this and that, provide some guidance as to why sheet X, why row 6 and why this cell and that cell. Why not sheet Y? Why not row 7? Also, you say you want to see this and that in your totals sheet. Where in your totals sheet? Imagine you just hired a guy off the street who knows nothing about landscaping and you want him to do all this for you. What would you tell him? HTH Otto "Tony" wrote in message ... I have a job costing spreadsheet that includes 7 worksheets from my different vendors and a labor worksheet which includes hourly rates, overhead, etc. This spreadsheet accurately tells me the cost for a job and currently i have a "totals" worksheet which simply sums any data input into the vendor worksheets and the labor worksheet. On each of the 7 vendor worksheets, there are materials, plants, trees,etc (landscaping company). I also include as columns, our cost, 15% markup price, 20% markup price and 25% markup. My totals worksheet shows my labor costs, along with a sum of the individual vendor worksheets at all levels of pricing (ie:our cost, 15%, 20%,, etc). The trick here is that I would like to see what comprises the order from each vendor on the summary page as well whereas now I only see the dollar totals. Ideally if in vendor worksheet 1, I need 10 items and in vendor sheet 2, i need 4 items, i would like to see the 10 items and the 4 items on my totals sheet along with all of the cost/pricing amounts. I havent been able to find any built in functions that can handle this so i'd be open to trying something like that as well. ANy help is greatly appreciated...thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
summarize multiple worksheets using VBA
THanks for the reply Otto, I'll try again.
Workbook composed of 8 worksheets for a landscaping company. Worksheets 1-7 are the different vendors i deal with for every job I do. Worksheet 8 is labor costs which is a simple calculation. All vendor worksheets have the same columns. They are Item #, Item Description, Amount, our price, our total, 15% Markup price, 15% total, 20% Markup price, 20% total, 25% Markup price, 25% total. Since all vendors offer differing quantity of items, the worksheets all have different row counts. The "our cost" column is simply a price list from the vendor and is filled in for each worksheet. For example, if i need to order stones, I will use vendor 1 (ie: worksheet 1 has a listing of all the stones I offer my customers to build retaining walls, etc). I enter a quantity of stones I would like to order from that vendor. So lets say the vendor offers 50 different types of stone (ie 50 rows of data), I enter the amount of stone i need for the job in the Amount column for the corresponding row of the stone the customer wants. My next 4 columns a Our Cost, Our Total (multiplies AMT * Our Cost), 15% Price (multiplies our cost *1.15), 15% total (multiplies AMT * 15% price), and the next 4 colums are the same as the 15% except with 20% and 25% markups depending on the job. Now that I have the stone for this job, I would like to enter data for plants, trees, bushes, etc. I go to worksheet 2 who is a different vendor and enter the materials i will need from that vendor in the same fashion as stone. Since all the columns are the same across the vendor worksheets, all i need to do again is enter data into the amount column and then my calculations proceed at the cost, 15%, 20% and 25% levels. I do not use every vendor for every job. So in this example i have used worksheets 1 and 2. WIthin those worksheets, I sum up the totals for the 8 cost/price fields and reference them on a totals worksheet. The totals worksheet currently looks like this: our cost 15% 20% 25% Vendor 1 totals from sheet1 for respective columns Vendor 2 totals from sheet 2 for respective columns .. .. Vendor8 tot from sheet 8 for respective columns Labor tot from labor sheet Overhead (fixed amount for all pricing) JOB TOTAL - this sums all the columns above So what i'd like to see on this totals sheet is everythign above along with the Item Description from each worksheet i've entered data into. So in my example, I'd like to see the stone description I ordered from vendor 1 along with the totals from that vendor and same for any worksheet i enter data into. I'm just not sure how to do this non-programmatically. I'd guess somethign to the effect (in English and not code): if the amount column is not null for all vendor worksheets, transfer the pricing info and description to the totals sheet. I hope this is better.....thanks for you help....if you need more info, please let me know Tony "Otto Moehrbach" wrote: Tony From what you say about what you have and what you want, I would venture a guess that it would be an easy task to write you some code to do it for you. But without a lot more information, it would be impossible to do so. Write down a procedure you would follow if you were doing what you want and you were doing it all manually. When you say you would go to sheet X and row 6 and copy cells this and that, provide some guidance as to why sheet X, why row 6 and why this cell and that cell. Why not sheet Y? Why not row 7? Also, you say you want to see this and that in your totals sheet. Where in your totals sheet? Imagine you just hired a guy off the street who knows nothing about landscaping and you want him to do all this for you. What would you tell him? HTH Otto "Tony" wrote in message ... I have a job costing spreadsheet that includes 7 worksheets from my different vendors and a labor worksheet which includes hourly rates, overhead, etc. This spreadsheet accurately tells me the cost for a job and currently i have a "totals" worksheet which simply sums any data input into the vendor worksheets and the labor worksheet. On each of the 7 vendor worksheets, there are materials, plants, trees,etc (landscaping company). I also include as columns, our cost, 15% markup price, 20% markup price and 25% markup. My totals worksheet shows my labor costs, along with a sum of the individual vendor worksheets at all levels of pricing (ie:our cost, 15%, 20%,, etc). The trick here is that I would like to see what comprises the order from each vendor on the summary page as well whereas now I only see the dollar totals. Ideally if in vendor worksheet 1, I need 10 items and in vendor sheet 2, i need 4 items, i would like to see the 10 items and the 4 items on my totals sheet along with all of the cost/pricing amounts. I havent been able to find any built in functions that can handle this so i'd be open to trying something like that as well. ANy help is greatly appreciated...thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
summarize multiple worksheets using VBA
Tony
This macro will do what you want. I assumed each vendor sheet has the name of the vendor as the name of the sheet, and row 1 is a header row and your data starts in row 2. I also assumed that you had a sheet named "Labor Costs" and a sheet named "Totals". "Totals" is the destination sheet. I assumed ALL the other sheets are vendor sheets. I also assumed that each of your vendor sheets incorporates formulas to produce all the markup values.. This macro loops through all the vendor sheets and for each vendor sheet it loops through all the cells of Column C, Amount. For any cell in Column C that is occupied, the code copies and pastes the things you said you want copied and it pastes it all to the Totals sheet. This is just a start since I'm sure that my idea of your layout is not what you have. Look it over and try it out with a copy of your file so that you won't damage any of your data. Come back with the changes you need. Otto Sub GetTotals() Dim ws As Worksheet Dim rColC As Range Dim i As Range Dim Dest As Range With Sheets("Totals") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each ws In ActiveWorkbook.Worksheets If ws.Name < "Labor Costs" And ws.Name < "Totals" And _ Not IsEmpty(ws.Range("A2").Value) Then With ws Set rColC = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Offset(, 2) For Each i In rColC If Not IsEmpty(i.Value) Then Dest.Value = ws.Name 'Vendor name if same as sheet name Dest.Offset(, 1).Value = i.Offset(, 2).Value 'Our Total Dest.Offset(, 2).Value = i.Offset(, 4).Value '15% Total Dest.Offset(, 3).Value = i.Offset(, 6).Value '20% Total Dest.Offset(, 4).Value = i.Offset(, 8).Value '25% Total Dest.Offset(, 5).Value = i.Offset(, -1).Value 'Description Set Dest = Dest.Offset(1) End If Next i End With End If Next ws End Sub "Tony" wrote in message ... THanks for the reply Otto, I'll try again. Workbook composed of 8 worksheets for a landscaping company. Worksheets 1-7 are the different vendors i deal with for every job I do. Worksheet 8 is labor costs which is a simple calculation. All vendor worksheets have the same columns. They are Item #, Item Description, Amount, our price, our total, 15% Markup price, 15% total, 20% Markup price, 20% total, 25% Markup price, 25% total. Since all vendors offer differing quantity of items, the worksheets all have different row counts. The "our cost" column is simply a price list from the vendor and is filled in for each worksheet. For example, if i need to order stones, I will use vendor 1 (ie: worksheet 1 has a listing of all the stones I offer my customers to build retaining walls, etc). I enter a quantity of stones I would like to order from that vendor. So lets say the vendor offers 50 different types of stone (ie 50 rows of data), I enter the amount of stone i need for the job in the Amount column for the corresponding row of the stone the customer wants. My next 4 columns a Our Cost, Our Total (multiplies AMT * Our Cost), 15% Price (multiplies our cost *1.15), 15% total (multiplies AMT * 15% price), and the next 4 colums are the same as the 15% except with 20% and 25% markups depending on the job. Now that I have the stone for this job, I would like to enter data for plants, trees, bushes, etc. I go to worksheet 2 who is a different vendor and enter the materials i will need from that vendor in the same fashion as stone. Since all the columns are the same across the vendor worksheets, all i need to do again is enter data into the amount column and then my calculations proceed at the cost, 15%, 20% and 25% levels. I do not use every vendor for every job. So in this example i have used worksheets 1 and 2. WIthin those worksheets, I sum up the totals for the 8 cost/price fields and reference them on a totals worksheet. The totals worksheet currently looks like this: our cost 15% 20% 25% Vendor 1 totals from sheet1 for respective columns Vendor 2 totals from sheet 2 for respective columns . . Vendor8 tot from sheet 8 for respective columns Labor tot from labor sheet Overhead (fixed amount for all pricing) JOB TOTAL - this sums all the columns above So what i'd like to see on this totals sheet is everythign above along with the Item Description from each worksheet i've entered data into. So in my example, I'd like to see the stone description I ordered from vendor 1 along with the totals from that vendor and same for any worksheet i enter data into. I'm just not sure how to do this non-programmatically. I'd guess somethign to the effect (in English and not code): if the amount column is not null for all vendor worksheets, transfer the pricing info and description to the totals sheet. I hope this is better.....thanks for you help....if you need more info, please let me know Tony "Otto Moehrbach" wrote: Tony From what you say about what you have and what you want, I would venture a guess that it would be an easy task to write you some code to do it for you. But without a lot more information, it would be impossible to do so. Write down a procedure you would follow if you were doing what you want and you were doing it all manually. When you say you would go to sheet X and row 6 and copy cells this and that, provide some guidance as to why sheet X, why row 6 and why this cell and that cell. Why not sheet Y? Why not row 7? Also, you say you want to see this and that in your totals sheet. Where in your totals sheet? Imagine you just hired a guy off the street who knows nothing about landscaping and you want him to do all this for you. What would you tell him? HTH Otto "Tony" wrote in message ... I have a job costing spreadsheet that includes 7 worksheets from my different vendors and a labor worksheet which includes hourly rates, overhead, etc. This spreadsheet accurately tells me the cost for a job and currently i have a "totals" worksheet which simply sums any data input into the vendor worksheets and the labor worksheet. On each of the 7 vendor worksheets, there are materials, plants, trees,etc (landscaping company). I also include as columns, our cost, 15% markup price, 20% markup price and 25% markup. My totals worksheet shows my labor costs, along with a sum of the individual vendor worksheets at all levels of pricing (ie:our cost, 15%, 20%,, etc). The trick here is that I would like to see what comprises the order from each vendor on the summary page as well whereas now I only see the dollar totals. Ideally if in vendor worksheet 1, I need 10 items and in vendor sheet 2, i need 4 items, i would like to see the 10 items and the 4 items on my totals sheet along with all of the cost/pricing amounts. I havent been able to find any built in functions that can handle this so i'd be open to trying something like that as well. ANy help is greatly appreciated...thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I summarize items chosen on mulitiple worksheets | Excel Worksheet Functions | |||
how do I combine multiple worksheets into one? not summarize | Excel Worksheet Functions | |||
summarize data from multiple worksheets | Excel Discussion (Misc queries) | |||
Summarize formula links between worksheets | Excel Programming | |||
Bond prices: summarize ranges from different worksheets | Excel Programming |