Copy part of Listrows in non-active worksheet in VBA

[helivw]

I open a workbook X
I would like to copy part of a listrow from a table from a non-active worksheet in Workbook X
When I activate the worksheet "Fin" in Workbook X
This code works

MyCust.DataBodyRange.Range(Cells(1, 1), Cells(1, 10)).Copy

(MyCust is the Listobject in Workbook X in Worksheets Fin)

When worksheet "Fin" in Workbook Y is activated, I receive error 1004
How can I solve this problem without activating the worksheet in Workbook X

Thanks

 

[JLGWhiz]

The 1004 error is probably caused by the Cells property not being qualified. Try this syntax

With MyCust.DataBodyRange
    .Range(.Cells(1, 1), .Cells(1, 10)).Copy
End with

 

[helivw]

Works great. Thanks a lot

One thing I do not understand:
To approach the first row <rows(1)> of the ListObject.BodyRange you need => .Range(.Cells(0, 1), .Cells(0, 10)).Copy instead of => .Range(.Cells(1, 1), .Cells(1, 10)).Copy
Any idea why?

Thanks</rows(1)>

 

[JLGWhiz]

Thre is no row 0. When you use the Cells property to form your cell reference, the first number inside the parentheses is the row number so neither Cells(0, 1) nor Cells(1, 0) would be valid because the first is trying to use row 0 and the secong is trying to use column 0. Both Rows and Columns index numbers have a minimum of 1. You are allowed to use the 0 or a blank in both Offset and Resize, eg. Offset(, 5) = five columns to right, or Resize(5) = a verical range of five cells including the reference cell. In both the latter cases, 0 would refer to the base reference cell row or column as applicable.

 

[helivw]

Thanks for the explanation

As I understand cells(0,..) is not working

Header1	Header2	Header3	Header4	Header5
a	a	a	a	a
b	b	b	b	b

<tbody>
</tbody>

but
----
If I use .Rows(1):
MyCust.DataBodyRange.Rows(1).Copy

The first row (with a's) is selected to be copied
----
If I use =>.Cells(1....

With MyCust.DataBodyRange
.Range(.Cells(1, 1), .Cells(1, x)).Copy
End With

The second row (with b's) is selected to be copied
----
If I use .Cells(0....

With MyCust.DataBodyRange
.Range(.Cells(0, 1), .Cells(1, x)).Copy
End With

The first row (with a's) is selected to be copied

What can be the reason we need Cells(0,...) ? Thanks

 

[JLGWhiz]

The explanation I gave applied to just the Excel worksheet. The code you are using applies to a list object (table) that is on the worksheet and apparently the rules are different for a table than for the worksheet. I don't use tables and seldome write code for them so I would have to do some research to verify exactly how the rows and columns references work in a table. But you could probably do that yourself by doing a web search for tutorials on "Excel Tables VBA".

 

[JLGWhiz]

I did a quick review of how the contents of tables are referenced and can see that when DataBodyRange is used, the header row is not included in the object data. So Cells(1, 1) of that range would be Cells(2, 1) of the overall table. So it is how you reference your range within the table that determines what is returned when you use the Cells property to return a range. There are several ways to roference ranges in a table, which I won't go into here. But they do not follow the standard conventions of worksheet 'Cells' references other than the first number inside the parentheses is for a row and the second is for a column, because the reference is being made inside another range object within a worksheet.Range object. Now that I have you thoroughly confused, I will let you think on it for a while.

 

[helivw]

1