title: Concatenate a retrieved range with line break (vbCrlf)?
body: <p>Started a new thread to adjust code I had written (with much help from @Doomenik as I am very new to VB thank you sir!)</p>
  
  <p>Essentially, my code cycles through each worksheet that represents a customer and sends the data from specified cells/ranges to a "Summary" sheet.</p>
  
  <p>It all works fine but what I can't seem to get it to do now is where a range is retrieved, to concatenate the values into one cell using a line break between each record.</p>
  
  <p>Examples:</p>
  
  <p>Customer Name: B9</p>
  
  <p>Products A: H10:H34</p>
  
  <p>Products B: H38:H49</p>
  
  <p>Other Products: Q10:Q20</p>
  
  <p>Online Access: R37</p>
  
  <p>Where Products A / Products B / Other Products will have multiple values, how can I get these concatenated to the cell it copies to in the Summary sheet as opposed to across the row?</p>
  
  <p>Example:</p>
  
  <p><strong>Current State:</strong> Product 1 (B1), Product 2 (C1), Product 3 (D1) etc (across row)</p>
  
  <p><strong>Preferred State:</strong></p>
  
  <p>(All in B1 with line break)</p>
  
  <p>Product 1</p>
  
  <p>Product 2</p>
  
  <p>Product 3</p>
  
  <p>Below is the code I have. I would really appreciate the help. This is a fantastic piece off code for anyone looking to do a Summary page and easily amended so please feel free to copy and use.</p>
  
  <pre><code>Sub Summary_All_Worksheets_With_Formulas()
      Dim Sh As Worksheet
      Dim Req As Worksheet
      Dim myCell As Range
      Dim sumCell As Object
      Dim ColNum As Integer
      Dim RwNum As Long
      Dim basebook As Workbook
          With Application
          .Calculation = xlCalculationManual
          .ScreenUpdating = False
      End With
  
      'Add a worksheet with the name "Requirements Gathering"
      Set basebook = ThisWorkbook
      Set Req = Worksheets("Requirements Gathering")
      'The links to the first sheet will start column 2
      ColNum = 0
  
      For Each Sh In basebook.Worksheets
          If Sh.Name &lt;&gt; Req.Name And Sh.Visible Then
              RwNum = 13
              ColNum = ColNum + 2
  
              'Copy the sheet name in the A column
  
              Req.Cells(RwNum, ColNum).Value = " "
                  For Each myCell In Sh.Range("B9,H10:H34,H38:H49,Q10:Q20,R37")
                    If myCell.Value &lt;&gt; "" Then
                      RwNum = RwNum + 1
                      Req.Cells(RwNum, ColNum).Formula = _
                      "='" &amp; Sh.Name &amp; "'!" &amp; myCell.Address(False, False)
                      Req.Cells.NumberFormat = "General"
  
                      myCell.Copy
  
                      Req.Cells(RwNum, ColNum).PasteSpecial Paste:=xlPasteFormats
                    End If
                  Next myCell
          End If
  
      Next Sh
  
      Req.UsedRange.Columns.AutoFit
      With Application
          .Calculation = xlCalculationAutomatic
          .ScreenUpdating = True
      End With
  
  
  End Sub
  </code></pre>