How to concatenate single cell from variable number of sheets?

[valeriewong1]

Hi guys,

I'm trying to concatenate data from cell A65 across a variable number of identical sheets to cell G7 on a separate worksheet. Does anyone know how to do this using a UDF, preferably with spaces between the data from each cell? Thanks in advance!

Valerie

 

[drsarao]

Welcome to Board!
Try this:

Function ConCatSht(cel As String) As String
ConCatSht = ""
For Each ws In Worksheets 'exclude the Master sheet
    If ws.Name <> "Master" Then ConCatSht = ConCatSht & " " & ws.Range(cel).Value
Next ws
End Function

Put this in a code module.

Usage:
=ConCatSht("A65")

Change the name "Master" above to sheet name where G7 data goes.

 

[valeriewong1]

Thanks for the quick reply! Just to clarify, though, I am working with a few other sheets besides the master sheet and the identical sheets where I want to copy the information from cell A65. Is there any way to exclude those sheets from the formula? Thanks again!

Welcome to Board!
Try this:

Function ConCatSht(cel As String) As String
ConCatSht = ""
For Each ws In Worksheets 'exclude the Master sheet
    If ws.Name <> "Master" Then ConCatSht = ConCatSht & " " & ws.Range(cel).Value
Next ws
End Function

Put this in a code module.

Usage:
=ConCatSht("A65")

Change the name "Master" above to sheet name where G7 data goes.

 

[drsarao]

This should work:

Function ConCatSht(cel As String) As String
ConCatSht = ""
For Each ws In Worksheets 'exclude the Master and other sheets
    If Not IsError(Application.Match(ws.Name, Array("Sheet1", "Sheet2", "Master"), 0)) Then
        ConCatSht = ConCatSht & " " & ws.Range(cel).Value
    End If
Next ws
End Function

Write all the sheet names to EXCLUDE within Array(...) above.

 

[valeriewong1]

I've tried inserting the following code into a code module and inserting the formula =ConCatSht("A65") into the destination cell G7, but nothing seems to be appearing in the cell. Any idea what I'm doing wrong? I've tried inserting the name of the Master sheet into the "Master" bit of the code, but no luck.

Function ConCatSht(cel As String) As String
ConCatSht = ""
For Each ws In Worksheets 'exclude the Master and other sheets
If Not IsError(Application.Match(ws.Name, Array("Pre-Meeting BG Info", "Handover Email", "Master"), 0)) Then
ConCatSht = ConCatSht & " " & ws.Range(cel).Value
End If
Next ws
End Function

 

[Marcelo Branco]

I think that drsarao meant

If IsError(Application.Match(ws.Name, Array("Pre-Meeting BG Info", "Handover Email", "Master"), 0)) Then
ConCatSht = ConCatSht & " " & ws.Range(cel).Value
End If

instead of

If Not IsError(....

to exclude sheets within the array.

M.

 

[drsarao]

Marcelo,
Thanks for the correction. Not is a bugbear for me!
It is indeed:

Function ConCatSht(cel As String) As String
ConCatSht = ""
For Each ws In Worksheets 'exclude the Master and other sheets
    If IsError(Application.Match(ws.Name, Array("Sheet1", "Sheet2", "Master"), 0)) Then
        ConCatSht = ConCatSht & " " & ws.Range(cel).Value
    End If
Next ws
End Function

 

[valeriewong1]

Thanks guys, everything is working great now

 

[drsarao]

You are welcome and thanks for the feedback.