Thanks so much for your help Luke. I installed the code from the tab on my
totals page and it worked, but I have a couple of questions. Here is the
exact code I entered in the module:

Function TotalConc(P7 As Range, TOTALS As String, Optional d As String = ", ")

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

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

End Function



I tried to put a range in (P7:P150), but it would not accept this. Does
that mean I have to enter in a new code for each of these cells (P7 through
P150)? Also, on my totals page I entered this formula to total the P7's:

=TotalConc(P7,"Total Sheet")

This did total P7, but also gave me a series of commas.

I'm new to UDF's, so I thank you in advance for your patience.


"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.