copy first three columns of table then rest of it

[ajm]

folks, a user has just decided that they need an extra column in a particular model. one of the source feeds is housed in a table ("Table1") on an adjacent worksheet. in the current model, the source feed is copied by selecting the table and then pasting it to a sheet used to combine the multiple feeds ("Combined"). there is no opportunity to include an extra column in the initial download of data so i need to insert a column into the "Combined" sheet which means splitting Table1 as its being copied over.

This is my current code for this step.

With Worksheets("Combined")
 ' copy filtered rows to Combined tab at A6
    Worksheets("T1 Download").Range("Table1").SpecialCells(xlCellTypeVisible).Copy
    .Cells(.Rows.Count, "b").End(xlUp).Offset(1, -1).PasteSpecial xlPasteValues

is there an easier way than using a named range for the the first three columns of the table and a named range for the rest of the table and then copying/pasting each in turn to the Combined sheet?

 

[frabulator]

Would it be possible to copy the whole table as normal, and then add a new column in the new doc once the original data is copied? You can save the data for the new column (if there is any) an an array or collection until you are ready to use it.

 

[ajm]

hey frabulator. there are other feeds coming into the model that already have this particular column included. so, i would rather split this table at the copy/paste stage. I have just found a method to copy the first three columns:

Worksheets("T1 Download").ListObjects("Table1").DataBodyRange.Columns("A:C").Select

I would not be using select in my finished version. just testing at the moment.

Now just have to figure out the column d to end column. suppose i could just use a hardcoded letter for the last column for now.

Worksheets("T1 Download").ListObjects("Table1").DataBodyRange.Columns("D:Z").Select

 

[Alex Blakenburg]

You have another thread for a formula option which seems to overlap with this one. Do you still need an answer on this thread ?

 

[Alex Blakenburg]

This is a bit rough but see if this is what you had in mind.

Sub Copy2Ranges()

    Dim combTbl As ListObject, srcTbl As ListObject
    Dim srcRng As Range
    Dim srcColsCnt As Long, srcCol2ndPt As Long, srcColStart As Long, scrColEnd As Long
    Dim combLastCell As Range
   
    Set srcTbl = Range("Table1").ListObject
    srcColsCnt = srcTbl.Range.Columns.Count
    srcCol2ndPt = 4                             ' Starting column no for 2nd group of columns
   
    Set combTbl = Range("Table2").ListObject
    Set combLastCell = combTbl.ListColumns(1).Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
   
    With srcTbl
        .ListColumns(1).DataBodyRange.Resize(, srcCol2ndPt - 1).Copy
            combLastCell.Offset(1).PasteSpecial Paste:=xlPasteValues
        .ListColumns(srcCol2ndPt).DataBodyRange.Resize(, srcColsCnt - srcCol2ndPt + 1).Copy
            combLastCell.Offset(1, srcCol2ndPt).PasteSpecial Paste:=xlPasteValues       ' Leaves 1 blank column
    End With
       
End Sub