summarize multiple worksheets using VBA

[Tony]

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 (ieur 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

 

[Otto Moehrbach]

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]

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 are: 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]

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