copy range from one table to another

Question

Latest code below.... Couple of issues -

1. paste copies data to Col3 on the destination sheet not col 1
2. I think may need to sort the source data by column a before selecting the data - I got an error saying could not run the command over multilple selections (unless there is another way to cup and paste it) 3 after the update has been done could do with resize of the destination table to include all the new rows added as part of the table...

I have spent time looking into solving all these myself so I don't want you to think I am not trying :-)

Appreciate all the help being given!

   
    Dim MFST_LastRow As Long
    
    ' Get work sheet
    Set wsf = Sheets("TASK - Map and Validation")
    Set wst = Sheets("MASTER - Supplier File")
    
    ' Get table
    Set tblMSFT = wst.ListObjects("MSF_Table")
    Set tblMAV = wsf.ListObjects("MAV_Table")
    
    'Sort Table
    

    
    End With
    
    
    ' Filter table
    tblMAV.Range.AutoFilter Field:=1, Criteria1:="New"
    
    ' Copy filtered table
    tblMAV.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
    Set visRange = tblMAV.DataBodyRange.SpecialCells(xlCellTypeVisible)
    Set visRange = Application.Intersect(visRange, wsf.Columns("B:R"))
    
    visRange.Copy
    ' Get the last data row #
    With tblMAV.Range
        MFST_LastRow = wst.Cells(.Cells.Count).Row
        MsgBox (MFST_LastRow)
    End With
    
    ' Paste as values
    Application.ScreenUpdating = False
    MsgBox ("B4 PASTE")
     wst.Cells(MFST_LastRow + 1, 1).PasteSpecial xlPasteValues
    
  
        'tblMAV.AutoFilter.ShowAllData
    MsgBox "New Records Added", vbExclamation, PROC_TITLE
       
   
    Application.ScreenUpdating = True
'========================================

Answer 1

Copy a Specified Number of Columns From One Table to Another

Option Explicit

Sub UpdateMasterSupplier()
    
    Const PROC_TITLE As String = "Update Master Supplier"
    Const FIRST_COLUMN As Long = 2
    Const COLUMNS_COUNT As Long = 2 ' 15 in your case according to comments
    
    Dim sws As Worksheet: Set sws = MapAndValidation
    Dim slo As ListObject: Set slo = sws.ListObjects("MAV_Table")
    
    Dim srg As Range:
    
    With slo
        If .ShowAutoFilter Then
            If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        End If
        .Range.AutoFilter Field:=1, Criteria1:="New"
        With .DataBodyRange.Columns(FIRST_COLUMN).Resize(, COLUMNS_COUNT)
            On Error Resume Next
                Set srg = .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
        .AutoFilter.ShowAllData
    End With
    
    If srg Is Nothing Then
        MsgBox "No matching data found.", vbExclamation, PROC_TITLE
        Exit Sub
    End If
    
    Dim dws As Worksheet: Set dws = MasterSupplierFile
    Dim dlo As ListObject: Set dlo = dws.ListObjects("MSF_Table")
    
    Dim drrg As Range:
    
    With dlo
        If .ListRows.Count = 0 Then
            Set drrg = .HeaderRowRange.Offset(1).Resize(, COLUMNS_COUNT)
        Else
            With .Range
                Set drrg = .Resize(1, COLUMNS_COUNT).Offset(.Rows.Count)
            End With
        End If
    End With
    
    Dim sarg As Range, sarCount As Long
    
    For Each sarg In srg.Areas
        sarCount = sarg.Rows.Count
        drrg.Resize(sarCount).Value = sarg.Value
        Set drrg = drrg.Offset(sarCount)
    Next sarg
    
    MsgBox "Update complete.", vbInformation, PROC_TITLE

End Sub

Answer 2

I highly recommend using Option Explicit to prevent misspelling variable names. (eg. tblMSFT vs tblMFST)

Option Explicit
Sub UpdateMasterSupplier()
    Dim wsf As Worksheet
    Dim wst As Worksheet
    Dim tblMSFT As ListObject
    Dim tblMAV As ListObject
    Dim MFST_LastRow As Long
    ' Get work sheet
    Set wsf = Sheets("MAPANDVALIDATION")
    Set wst = Sheets("MASTERSUPPLIERFILE")
    ' Get table
    Set tblMSFT = wst.ListObjects("MSF_Table")
    Set tblMAV = wsf.ListObjects("MAV_Table")
    ' Filter table
    tblMAV.Range.AutoFilter Field:=1, Criteria1:="New"
    ' Copy filtered table
    tblMAV.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
    ' Get the last data row #
    With tblMAV.Range
        MFST_LastRow = .Cells(.Cells.Count).Row
    End With
    ' Paste as values
    wst.Cells(MFST_LastRow + 1, 1).PasteSpecial xlPasteValues
    MsgBox ("Update Complete")
End Sub

Microsoft reference document:

Option Explicit statement

---

Update

Question:the source table is in the TASK - MAP and Validation sheet and is MAV_Table - there is data in A to AN but I only want to copy B to P to MSF_Table A to O

    ' Copy filtered table
    Dim visRange As Range
    Set visRange = tblMAV.DataBodyRange.SpecialCells(xlCellTypeVisible)
    Set visRange = Application.Intersect(visRange, wsf.Columns("B:O"))
    visRange.Copy