Concatenate Text Strings from Single Cells of Variable number of Worksheets

[Pitmo]

Hello, Similar to the request below:

https://www.mrexcel.com/forum/excel...enate-single-cell-variable-number-sheets.html

I need to concatenate/combine text stings from a variable number of worksheets into a single cell, with each result separated by a line feed. I'd like to ignore worksheets that have no results.

To explain the layout:
I have three worksheets within the workbook that are out of scope.
I have one 'Results' worksheet and multiple 'Survey Response' worksheets which can range from one to 30+. The 'Survey Response' worksheets are all set within two hidden 'bookends' named 'Start' and 'End'.

I need to compile text found in 'E4' on the 'Survey Response' worksheets into 'E5' on the 'Results' worksheet. With each string of text separated by a line feed.

There are 30 or so more repetitions of this.

e.g. 'E5' on the 'Survey Response' worksheets to 'E6' on the 'Results'
'E7' on the 'Survey Response' worksheets to 'E8' on the 'Results'and so on.

Assume this needs to be VB as my concatenate formula only works for a fixed number of worksheets.

Thanks
Pitmo

 

[bhos123]

You are welcome