VBA Code for concatenating text from multiple (dynamic no of) worksheets

Hi all,

I am looking for a VBA code that will allow me to concatenate text from across multiple worksheets. To give some background, I have a survey on an excel worksheet that I send out for team members to complete. I then have a results workbook which contains VBA code to pull all the surveys in and a results worksheet which tallys up all the responses. It all works great for calculating scores, however, I also ask for comments and there are some open questions. My plan is collect up all the written responses with a comma between them.

I started off with a long formula in each of the cells, see below, which worked for a set number of 20 results with known worksheet names.

=CONCATENATE('Survey Results 1'!L24,",",'Survey Results 2'!L24,",",'Survey Results 3'!L24,",",'Survey Results 4'!L24,",",'Survey Results 5'!L24,",",'Survey Results 6'!L24,",",'Survey Results 7'!L24,",",'Survey Results 8'!L24,",",'Survey Results 9'!L24,",",'Survey Results 10'!L24,",",'Survey Results 11'!L24,",",'Survey Results 12'!L24,",",'Survey Results 13'!L24,",",'Survey Results 14'!L24,",",'Survey Results 15'!L24,",",'Survey Results 16'!L24,",",'Survey Results 17'!L24,",",#REF!,",",'Survey Results 19'!L24,",",'Survey Results 20'!L24,"")

I have now created a dynamic Index List on a sheet called Key which updates based on the names of the worksheets. I am thinking some kind of code which will use the Worksheets from the Index List and pull all data from a specific cell, for the above example cell L24 and will concatenate it into a specified cell.

Is this possible?


Thanks,
Adam

Here is a different approach:

Please Login or Register  to view this content.

Hi both,

Thank you for your help although I must admit I am a little lost and not sure how to make the codes above work for my workbook. I have attached my workbook incase either of you (or someone new) is able to amend it to work? I haven't worked on this project for a while but would like to complete it for next week and this is the last step.

Any help would be much appreciated.

I have also attached the survey that is sent out in case this makes it easier. You will notice the survey row numbers do not align the 'Results Table' tab on the results workbook, this is due to removing the 'Name (Optional)' row.

I would like the macro to pull in all comments from applicable cells in column L along with the comments to the questions from row 40 down.

Many thanks

Hi Bernie, thank you for your help.

I have tried the macro and cannot get it do what I want. I actually can't see that it does anything, sorry!

I would like the macro to copy text from cell A40 on any 'Survey Results' sheets that exist and copy it into cell A39. I think once I have the macro working for one cell I will then be able to copy the sub for other cells.

I have tried to amend the code to:

Sub FillWhatWentWell()
Dim wSht As Worksheet
Dim strResp As String

For Each wSht In ThisWorkbook.Worksheets
If wSht.Name Like "*Survey Results*" Then
strResp = strResp & IIf(strResp <> "", ", ", "") & wSht.Range("A40").Value
End If
Next wSht

ThisWorkbook.Worksheets("Results Table").Range("A39") = strResp
End Sub

But I still cannot get it to work.

Thanks for your help

Hi Bernie,

I seem to have corrected it.

I had to change

If wSht.Name Like "*Survey Results*" Then

to

If wSht.Name Like "*Survey*" Then

I will keep going and hopefully it will all now work. Thanks

That works brilliantly, thank you very much!

Very much appreciated!!

Latest version attached.