VBA copy table data to another sheet by using column header

June 28, 2023

I am new to vba. I plan to copy data from certain columns in a table to another worksheet by using the column header names rather than column numbers. Such as if the first column is named as "ID", should refer this column as Range("ID") rather than Range("A"). I searched online but still have no clue on how to edit my code. It would be much appreciated for any suggestions.

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

>Solution :

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