title: VBA - PasteSpecial not working after using SpecialCells.Copy
body: <p>To sum up, I try to copy some filtered data from a workbook A to a workbook B keeping the formatting of the workbook B. </p>
  
  <p>Here is the relevant part of my code: </p>
  
  <pre><code>With originSheet
      .AutoFilterMode = False
      With .Range("A7:AA" &amp; lastRowOriginSheet)
          .AutoFilter Field:=2, Criteria1:=projectNumber
          .SpecialCells(xlCellTypeVisible).Copy
      End With
  End With
  destinationSheet.Range("B4").PasteSpecial xlPasteValues
  </code></pre>
  
  <p>The paste special is not working and this is the formatting of the workbook A that is used.</p>
  
  <hr>
  
  <p>Solved:</p>
  
  <p>The problem was that you can't use PasteSpecial in a discontinuous range. </p>
  
  <p>So I went with the solution of Siddharth Rout to go through all the areas of the filtered range: </p>
  
  <pre><code>        With originSheet
              .AutoFilterMode = False
  
              With .Range("A7:AA" &amp; lastRowOriginSheet)
                  .AutoFilter Field:=2, Criteria1:=projectNumber
  
                  Set filteredRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
  
                  '~~&gt; Loop through each area
                  For Each area In filteredRange.Areas
                      With destinationSheet
                          '~~&gt; Find Next available row
                          lRow = .Range("B" &amp; .Rows.Count).End(xlUp).Row + 1
  
                          area.Copy
                          destinationSheet.Range("B" &amp; lRow).PasteSpecial xlPasteValues
                      End With
                  Next area
              End With
          End With
  </code></pre>