multiple conditional sum? or average?

[Cablelane]

Any help on this appreciated. I'm new to this. Currently using Excel '03.

I have a workbook w/ one worksheet for each of ~20 employees to track patients they see. Each sheet is essentially the same: rows are for dates someone works, columns are “Date”, “Day of week” (calculated), “Scheduled”, “Cancel”, “No-show”, “CA/NS rate” (calculated). I’d like to come up w/ a department average, per month, on a separate “Tally” sheet. It may be helpful, down the road, to look at day of week or other variables. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Solutions I’ve tried: <o></o>
1) pivot table: worked well on individual sheets, but couldn’t get it to calculate across all sheets, based on month.<o></o>
2) SUMIF: trying to add each column above onto the tally sheet, then recalculate CA/NS rate; again, couldn’t get it to conditionally add (in other words, 2/23/09 may be on row 2 for me, but not present at all on someone else’s sheet if they didn’t work at all, so I can’t just add up same cell on different sheets).

Thanks again for help!

<o></o>

 

[Yard]

edit: I think this works now

Sub CompileSheets()
Dim ws As Worksheet, wsTally As Worksheet, rngCopy As Range, i As Integer
 
Set wsTally = ThisWorkbook.Worksheets("Tally")
wsTally.UsedRange.ClearContents

i = 0
 
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = wsTally.Name Then GoTo NextWs
        If i > 0 Then GoTo CopyValues
            Set rngCopy = ws.Range(ws.Cells(1, 1), ws.Cells(1, 1).End(xlToRight))
            rngCopy.Copy
            wsTally.Cells(1, 1).Value = "Sheet Name"
            wsTally.Cells(1, 2).PasteSpecial xlPasteValues
                
CopyValues:
        
        With ws
               Set rngCopy = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 6)
        End With
        rngCopy.Copy
        With wsTally
            With .Cells(.Rows.Count, 1).End(xlUp).Resize(rngCopy.Rows.Count, 1)
                .Value = ws.Name
                .Offset(0, 1).Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
            End With
        End With
        
NextWs:
Next ws
End Sub

 

[Cablelane]

Wow! Looks like an awesome solution! But I'm not sure how to use it. Opened VBA editor, copied and pasted; obviously I'm missing something. Can you give me some more help on writing this code into my workbook please?

Thank you for your help!!

 

[Yard]

First, you have to paste the code into a standard code module. In the VBEditor, from the Project Explorer window (if you can't see that you can display it via the View menu), right-click your workbook and choose Insert > Module.

Paste the code in there.

Secondly, you need some method to run it. You can always just make sure your cursor is somewhere within the code and hit Run from the VBE, but that's a bit clumsy. You can assign macros to Form buttons, shapes etc., or you can have it run on certain workbook events, like every time you save etc....

Decide how/when you want to update your Tally sheet and take it from there.