Move Columns in ListObject

In the attached, there is a Table, which I copy to another spreadsheet, but then I need to re sort the columns.

So I require Centre to come after sub, and Move description to after voucher No.

I am not sure how best to accomplish this.

Thanks for your assistance.

RoyUk. Currently the workbook that the table is copied to, already has many sheets (it is quite complex book) - So basically the table as is is copied.

The other point is that the sheet I copy from is a long standing form that others use, so I had thought about re arranging the columns in the orginal, but as other use it, I am hesitant to do that.

Once that table is in the main book, I then copy the table (with the re-arranged) columns to a specific sheet within the book (there is a choice of 4 different sheets the table is copied to.) (Other stuff happens between one copy and the another.)

I would like to be able to re-arrange once the table is in the new sheet (before then being copied to the final destination sheet.

I hope that makes sense.

BTW, as the destination book is quite complex, with lots of data, it is difficult to be able to attach the file.

Ok, I spent a little bit more time on this, and I have come up with:

With ActiveSheet.ListObjects("Table1")
    .ListColumns.Add(4).Name = "Ctr"
    .ListColumns("Ctr").DataBodyRange = .ListColumns("Centre").DataBodyRange
    '.ListColumns("Centre").Delete
    .ListColumns.Add(7).Name = "Desc"
    .ListColumns("Desc").DataBodyRange = "Chq No " & .ListColumns("Chq No.").DataBodyRange & " " & .ListColumns("Description").DataBodyRange
End With

but this only works if there is one row of data.

Any further help would be appreciated.

It could, except that in the actual version I want to copy from, as 2 tables, and the headers are slightly different, in as much as it has one extra column. Essentially when that table is in the main workbook, I concatenate that into the description column (and then delete the original column).

Could I do something like

With ActiveSheet.ListObjects("Table1")
    .ListColumns.Add(4).Name = "Ctr"
    For Each cell In .ListColumns("Ctr").Range
    .ListColumns("Ctr").DataBodyRange = .ListColumns("Centre").DataBodyRange
    Next
    '.ListColumns("Centre").Delete
    .ListColumns.Add(7).Name = "Desc"
    .ListColumns("Desc").DataBodyRange = "Chq No " & .ListColumns("Chq No.").DataBodyRange & " " & .ListColumns("Description").DataBodyRange
End With

Thank you for your response.

I have been trying different ways, and it might be that you are unable to concatenate with .ListObjects.

I am finding that this code:

With ActiveSheet.ListObjects("Table1")
    .ListColumns.Add(4).Name = "Ctr"
    .ListColumns("Ctr").DataBodyRange.Value = .ListColumns("Centre").DataBodyRange.Value


    '.ListColumns("Centre").Delete
    .ListColumns.Add(7).Name = "Desc"
    .ListColumns("Desc").DataBodyRange.Value = .ListColumns("Chq No.").DataBodyRange.Value & .ListColumns("Description").DataBodyRange.Value
        'above line give Type Mismatch error 1003
End With

works perfectly, if in the final line I only have one value either

= .ListColumns("Chq No.").databodyrange.value

or

= .listcolumns("Description").databodyrange.value

If I try and have both, or even any text, such as

"Chq" & " " & .ListColumns("Chq No.").databodyrange.value

then the line will give a Type Mismatch Error

Thanks all for your help, this code seems to do what I require:

Dim I as Integer
With ActiveSheet.ListObjects("Table1")
    .ListColumns.Add(4).Name = "Ctr"
    .ListColumns("Ctr").DataBodyRange.Value = .ListColumns("Centre").DataBodyRange.Value
    .ListColumns.Add(7).Name = "Desc"
    For i = 1 To .ListRows.Count


        .DataBodyRange(i, 7) = "Chq " & .DataBodyRange(i, 9) & " " & .DataBodyRange(i, 5)
    Next i
    '.ListColumns("Centre").Delete
    '.ListColumns("Description").Delete
End With