title: Excel VBA copy from af filtered list with hidden column
body: <p>Try this. First establish the following datatable on sheet 1</p>
  
  <pre><code>A   B   C   D
  a1  b1  c1  d1
  a2  b2  c2  d2
  a3  b3  c3  d3
  a4  b4  c4  d4
  a5  b5  c5  d5
  </code></pre>
  
  <p>Create a named area "Dateset" A2:D6.
  Put a filter on the table and hide column B and filter the table to show only the first row.</p>
  
  <p>Now test the following code on the dataset:</p>
  
  <pre><code> Sub DataCopy()
     Dim r
     For Each r In Names("Dateset").RefersToRange.Rows
        r.Copy Worksheets(2).Range("a65536").End(xlUp).Offset(1, 0)
     Next
   End Sub
  </code></pre>
  
  <p>I get the following result:</p>
  
  <pre><code>a1  c1  d1  
  a2  b2  c2  d2
  a3  b3  c3  d3
  a4  b4  c4  d4
  a5  b5  c5  d5
  </code></pre>
  
  <p>Without the filter I get (as expected):</p>
  
  <pre><code>a1  b1  c1  d1
  a2  b2  c2  d2
  a3  b3  c3  d3
  a4  b4  c4  d4
  a5  b5  c5  d5
  </code></pre>
  
  <p>Can you help me with a way to get all the data - also from the visible row?</p>
  
  <p>Ps. I have also tried:</p>
  
  <pre><code>Dim c as Range
  For Each c In Range("A2", Range("A6")).Cells
      Range(c, c.End(xlToRight)).Copy Worksheets(2).Range("A65536").End(xlUp).Offset(1, 0)
      c.EntireRow.Copy Worksheets(2).Range("A65536").End(xlUp).Offset(1, 0)
      Range(c, c.Offset(0, 5)).Copy Worksheets(2).Range("A65536").End(xlUp).Offset(1, 0)
  Next
  </code></pre>
  
  <p>but with no success...
  I have tested in Excel 2007 and 2010.</p>