excel 使用列标题将表数据复制到另一个工作表

6psbrbz9 于 12个月前发布在其他

关注(0)|答案(1)|浏览(146)

我想使用列标题名称而不是列编号将数据从表中的某些列复制到另一个工作表。
如果第一列命名为“ID”，则将此列称为Range（“ID”）而不是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

字符串

excel

来源：https://stackoverflow.com/questions/76568673/copy-table-data-to-another-sheet-by-using-column-header

1条答案

按热度按时间

9rnv2umw1#

如果您只需要传输值，则可以直接执行，而无需复制/粘贴：

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

字符串

赞(0）回复(0）举报 12个月前

我来回答

excel 使用列标题将表数据复制到另一个工作表

1条答案

相关问题

热门标签

最新问答