Move Columns in ListObject

  • 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:


    Code
    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

    Code
    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
  • I think you will need to copy the individual columns separately to the correct locations.


    If you had a Table set up with headers I would suggest using AdvancedFilter automated with a macro.

  • 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:

    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

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

    or

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


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

    Code
    "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:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!