Excel VBA copy from af filtered list with hidden column

Try this. First establish the following datatable on sheet 1

A   B   C   D
a1  b1  c1  d1
a2  b2  c2  d2
a3  b3  c3  d3
a4  b4  c4  d4
a5  b5  c5  d5

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.

Now test the following code on the dataset:

 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

I get the following result:

a1  c1  d1  
a2  b2  c2  d2
a3  b3  c3  d3
a4  b4  c4  d4
a5  b5  c5  d5

Without the filter I get (as expected):

a1  b1  c1  d1
a2  b2  c2  d2
a3  b3  c3  d3
a4  b4  c4  d4
a5  b5  c5  d5

Can you help me with a way to get all the data - also from the visible row?

Ps. I have also tried:

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

but with no success... I have tested in Excel 2007 and 2010.

That definitely looks like a bug.

Since it's only the first row that seems to be the problem, you could copy THAT row column by column, but copy the remaining rows by entire row:

Sub DataCopy()
    Dim r As Range
    Dim dest As Range
    For Each r In Names("DateSet").RefersToRange.Rows
        If r.Row = 2 Then
            For Each c In r.Columns
                Set dest = Worksheets(2).Cells(r.Row, c.Column)
                r.Cells(1, c.Column).Copy dest
            Next
        Else
            Set dest = Worksheets(2).Cells(r.Row, r.Column)
            r.Copy dest
        End If
    Next
End Sub

Here is a stab in the dark:

 Sub DataCopy()

    Dim vArray As Variant

    vArray = Names("Dateset").RefersToRange.Value

    Worksheets(2).[A1].Resize(UBound(vArray, 1), UBound(vArray, 2)).Value = vArray

 End Sub

Basically you should not be copying rows like that in those circumstances ie hidden and filtered rows.

If you want a better answer you should describe EXACTLY what you are trying to do. Your examples and explanation are confusing. To me anyway :) I just don't understand what you are really trying to do.