Copy table data to another sheet by using column header

Question

I want to copy data from certain columns in a table to another worksheet by using the column header names rather than column numbers.
If the first column is named "ID", refer to this column as Range("ID") rather than Range("A").

Sub Program()
    Dim ws1 As Worksheet, ws2 As Worksheet, lRow As Long
    Dim table As ListObject
    Set ws1 = ThisWorkbook.Sheets("WeeklyData")
    Set ws2 = ThisWorkbook.Sheets("MonthlyData")
    Set table = ws1.ListObjects.Item("WeeklyTable")
   
    'Find first empty row where values should be pasted in MonthlyData sheet
    With Worksheets("MonthlyData")
        j = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
    End With
   
    'Find last row of data in WeeklyData sheet
    lRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row

    'Only copy data if WeeklyData sheet has data
    If lRow > 1 Then
        With ws1
             table.ListColumns("ID").DataBodyRange.Copy Destination.PasteSpecial xlPasteValues=ws2.range("A" & j)
        End With
    End If
   
End Sub

Answer 1

If you only need to transfer values you can do that directly without copy/paste:

Sub Program()
   
   Dim ws1 As Worksheet, ws2 As Worksheet, lRow As Long
   Dim table As ListObject, j As Long
   
   Set ws1 = ThisWorkbook.Sheets("WeeklyData")
   Set ws2 = ThisWorkbook.Sheets("MonthlyData")
   Set table = ws1.ListObjects.Item("WeeklyTable")
   
   'Find first empty row where values should be pasted in MonthlyData sheet
   With Worksheets("MonthlyData")
        j = .Cells(.Rows.Count, "B").End(xlUp).row + 1
   End With
   
   'Only copy data if WeeklyData sheet has data
   If Application.CountA(table.DataBodyRange) > 0 Then
     CopyValues table.ListColumns("ID").DataBodyRange, ws2.Range("A" & j)
     '...other columns here
   End If
   
End Sub

'copy values from `rngSrc` to `rngDest`
Sub CopyValues(rngSrc As Range, rngDest As Range)
    rngDest.Cells(1).Resize(rngSrc.Rows.Count, _
                            rngSrc.Columns.Count).Value = rngSrc.Value
End Sub