title: Copying Range and ignoring blanks from all worksheets to 'Summary' Sheet
body: <p>I am in desperate need of help and have been racking my brain for a few days now.</p>
  
  <p>Essentially, I am trying to create code (I am very new to VBA) that will go through all worksheets and copy these cells and/or ranges to a <code>Summary</code> Sheet. I need it to copy only when data exists, so I ignore any blanks.</p>
  
  <p>The cells/ranges I want to copy across are:</p>
  
  <pre><code>B5
  H10:H34 
  H38:H49 
  R37 
  Q10:Q20
  </code></pre>
  
  <p>Essentially the data would be displayed as:</p>
  
  <p>Client Name: <code>B5</code></p>
  
  <p>Products from Group A: <code>H10:H34</code> (ignoring blank cells)</p>
  
  <p>Products from Group B: <code>H38:H49</code> (ignoring blank cells)</p>
  
  <p>Online Service Requested: <code>R37</code></p>
  
  <p>External Services Selected: <code>Q10:Q20</code> (ignoring blank cells)</p>
  
  <p>I have written code that will cycle through each worksheet but cannot seem to get it to work for the ranges and ignoring blank cells.</p>
  
  <p>Could someone please help me? This is my code so far:</p>
  
  <pre><code>Sub Summary_All_Worksheets_With_Formulas()
      Dim Sh As Worksheet
      Dim Req As Worksheet
      Dim myCell As Range
      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 = 1
  
      For Each Sh In basebook.Worksheets
          If Sh.Name &lt;&gt; Req.Name And Sh.Visible Then
              RwNum = 16
              ColNum = ColNum + 1
              Columns("C:C").Insert , CopyOrigin:=xlFormatFromLeftOrAbove
  
              'Copy the sheet name in the A column
              Req.Cells(RwNum, ColNum).Value = Sh.Name
                  For Each myCell In Sh.Range("B5,R37")
                  RwNum = RwNum + 1
                  Req.Cells(RwNum, ColNum).Formula = _
                  "='" &amp; Sh.Name &amp; "'!" &amp; myCell.Address(False, False)
                  Req.Cells.NumberFormat = "General"
  
                  Next myCell
          End If
  
      Next Sh
  
      Req.UsedRange.Columns.AutoFit
      With Application
          .Calculation = xlCalculationAutomatic
          .ScreenUpdating = True
      End With
  End Sub
  </code></pre>
  
  <p>Then I want the data to display in the Summary sheet across the columns so sheet 1 data in column A sheet 2 in column B etc.</p>
  
  <p>I know I am probably asking a hell of a lot but I just can't work this one out.</p>
  
  <p>Super appreciation in advance for anyone who could help me.</p>