Concatenate first:last! worksheet

[JStiehl]

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.

 

[Luke M]

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","; ")

 

[JStiehl]

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 (P7150), 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.

 

[JStiehl]

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

 

[JStiehl]

I have resolved the other issues I was having, but am having trouble with
this function working on new spreadhsheets I create based on this one. Is
there something extra I need to do? Thanks in advance for your help.

 

[Margaret]

You can ignore blank cells with something like:

If ws.Range(r.Address).Value <> "" Then
TotalConc = TotalConc & ws.Range(r.Address).Value & d
x = x + 1
End If

You have to initialize x to 0 at the outset. The incrementing is there in
case all the cells are blank. If that happens, your concatenated string has a
length of 0 and attempting to strip the last separator causes a #VALUE error.

So end the function with something like

If x > 0 Then
TotalConc = Left(TotalConc, Len(TotalConc) - Len(d))
End If