I got it to work now, my only problem is finding a way to ignore blank cells.
It puts a string seperator in for blank cells--is there any way to prevent
this? Thanks again so much.

Here is your UDF I used:

Function TotalConc(r As Range, s As String, Optional d As String = ", ")

x = 0
For Each ws In ThisWorkbook.Worksheets
If ws.Name < s Then
TotalConc = TotalConc & ws.Range(r.Address).Value & d
End If
Next

TotalConc = Left(TotalConc, Len(TotalConc) - Len(d))

End Function

Here is the formula I used in my workbook to test it:

=TotalConc(P7,"Totals Sheet")

"Luke M" wrote:

This UDF might help you. To install: Right-click on sheet tab, view code.
Insert - Module. Paste this in. Close VBE.

'===========
Function TotalConc(r As Range, s As String, Optional d As String = ", ") As
String
'r is the cell you want to concatenate
's is the sheet you don't want to include in concatenation
'd is the string seperator you want to use

x = 0
For Each ws In ThisWorkbook.Worksheets
If ws.Name < s Then
TotalConc = TotalConc & ws.Range(r.Address).Value & d
End If
Next

TotalConc = Left(TotalConc, Len(TotalConc) - Len(d))

End Function
'=================

Your formula in your workbook then becomes something like:
=TotalConc(P6,"Total Sheet")

Note that this formula does not require bookend sheets (such as "first" and
"last") and that you tell the formula which sheet you don't want to include
in the concatenation. Also, you can choose a string seperator if you want,
the default it a comma. To use a semicolon:
=TotalConc(P6,"Total Sheet","; ")

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"JStiehl" wrote:

I need to concatenate 150 rows in 50 different worksheets for a totals page,
and may need to add additional worksheets in later versions. So, I need P7
in all 50 worksheets to be concatenated on P6 in the totals page, P8 in all
worksheets to be concatenated on P7 in the totals page, etc. I am wondering
if it's possible to use something like first:last! to concatenate the
worksheet range, making it possible to include additional sheets added in
before the current last worksheet.

I am using "&" for the concatenate function now as I have more than 30
sheets to concatenate, but this means new sheets I add in have to be manually
entered into the formula.

Thanks in advance for your help.