consolidating sales sheets

[Guest]

I am responsible for 6 sales people on the east coast. I have a sheet for each salesperson (within the same shared network based workbook) to track their sales with sections (column & row headings) for forecasted sales, pipeline and beyond. All they need to do is insert new rows and enter the cell contents. Is there a convienent way for me to combine these three sheets each month to see how the whole area is performing.

 

[Guest]

sorry a typo: it's consolidating the 6 sheets into a combined sheet within the same workbook.

 

[Dave Peterson]

If the workbook is shared, then that combined sheet has to already exist--you
can't add a worksheet to a shared workbook.

So I'm gonna use Combined as the name of the sheet that gets all the data. I'm
also guessing that row 1 the only row that contains headers and that header row
is already on Combined.

Option Explicit
Sub combineWks()

Dim CombWks As Worksheet
Dim wks As Worksheet
Dim dummyRng As Range
Dim rngToCopy As Range
Dim DestCell As Range

Set CombWks = Worksheets("combined")

With CombWks
'clean up existing data, but keep headers
.Range("a2", .Cells(.Rows.Count, .Columns.Count)).ClearContents
Set DestCell = .Range("a2")
End With

For Each wks In Worksheets
If wks.Name = CombWks.Name Then
'do nothing
Else
With wks
'try to reset lastused cell
Set dummyRng = .UsedRange
Set rngToCopy _
= .Range("a2", .Cells.SpecialCells(xlCellTypeLastCell))
End With
With rngToCopy
DestCell.Resize(.Rows.Count, .Columns.Count).Value _
= rngToCopy.Value
Set DestCell = DestCell.Offset(.Rows.Count)
End With
End If
Next wks

End Sub

 

[Guest]

Dave
Your assumption is correct. The combined sheet already exists and it is the one that gets all the data combined. You code looks very interesting, but I don't know what to do with it or where to put it. please advise
Thanks very much
Gino.

 

[Dave Peterson]

Since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there. But change the cell addresses to save and the names of
the worksheets.

Now go back to excel.
click on Tools|macro|macros...
click on the macro name (combineWks)
and then click run.

========

The workbook must not be shared for you to add the macro. If you can't unshare
it, then you can put the macro in a separate workbook and then with your
"combination" workbook active, do the tools|macro stuff.