title: Merge Multiple Excel Sheets Into Summary Sheet
body: <p>I wonder whether someone may be able to help me please.</p>
  
  <p>I'm using the code below to allow the user to copy from multiple Excel workbooks and merge the data into a Summary sheet.</p>
  
  <pre><code>Sub Merge()
          Dim DestWB As Workbook, WB As Workbook, WS As Worksheet, SourceSheet As String
  
          Set DestWB = ActiveWorkbook
          SourceSheet = "Input"
          startrow = 7
          FileNames = Application.GetOpenFilename( _
          filefilter:="Excel Files (*.xls*),*.xls*", _
          Title:="Select the workbooks to merge.", MultiSelect:=True)
          If IsArray(FileNames) = False Then
              If FileNames = False Then
                  Exit Sub
              End If
          End If
          For n = LBound(FileNames) To UBound(FileNames)
              Set WB = Workbooks.Open(Filename:=FileNames(n), ReadOnly:=True)
              For Each WS In WB.Worksheets
                  If WS.Name = SourceSheet Then
                      With WS
                          If .UsedRange.Cells.Count &gt; 1 Then
                              dr = DestWB.Worksheets("Input").Range("C" &amp; Rows.Count).End(xlUp).Row + 1
                              lastrow = .Range("C" &amp; Rows.Count).End(xlUp).Row
                              For j = lastrow To startrow Step -1
                                  If Range("E" &amp; j) &lt;&gt; "Requirements Manager" And Range("E" &amp; j) &lt;&gt; "R &amp; D Lead" And Range("E" &amp; j) &lt;&gt; "Technical" And Range("E" &amp; j) &lt;&gt; "Analyst" Then Rows(j).Delete
                              Next
                              lastrow = .Range("C" &amp; Rows.Count).End(xlUp).Row
                              If lastrow &gt;= startrow Then
                                  .Range("A" &amp; startrow &amp; ":AQ" &amp; lastrow).Copy
                                  DestWB.Worksheets("Input").Cells(dr, "A").PasteSpecial xlValues
                              End If
                          End If
                      End With
                      Exit For
                  End If
              Next WS
              WB.Close savechanges:=False
          Next n
      End Sub
  </code></pre>
  
  <p>The code works fine but I'm stuck with a problem related to the copying of the information, which is this line of code:</p>
  
  <pre><code>  .Range("A" &amp; startrow &amp; ":AQ" &amp; lastrow).Copy
  </code></pre>
  
  <p>I need to change this so that it takes into account two ranges. These are columns "B:AD" and "AF:AQ", but I'm not sure how to do this.</p>
  
  <p>I just wondered wehether someone could possibly take a look at this please and offer some guidance on how I may go about solving this.</p>
  
  <p>Many thanks and kind regards</p>