VBA列参考来自单元格范围

Question

我有一个非常简单的关于单元格维的问题和列属性的问题。

对于上下文 - 我有三张纸，其中一张是主表。我将根据列标题将两个数据表中每个数据的数据复制到主机中。并非主选项卡中的所有列标题都出现在数据表中。我编写了以下代码，这些代码通过第一个数据表（WS2）循环，然后将数据列粘贴到主表中的相关列中。

。

Sub master_sheet_data()
'Variables
Dim ws1_xlRange As Range
Dim ws1_xlCell As Range
Dim ws1 As Worksheet
Dim ws2_xlRange As Range
Dim ws2_xlCell As Range
Dim ws2 As Worksheet
Dim ws3_xlRange As Range
Dim ws3_xlCell As Range
Dim ws3 As Worksheet
Dim valueToFind As String
Dim lastrow As String
'Assign variables to specific worksheets/ranges
'These will need to be updated if changes are made to the file.
Set ws1 = ActiveWorkbook.Worksheets("Refined event data - all")
Set ws1_xlRange = ws1.Range("A1:BJ1")
Set ws2 = Worksheets("Refined event data")
Set ws2_xlRange = ws2.Range("A1:BJ1")
Set ws3 = Worksheets("Refined ID data")
Set ws3_xlRange = ws3.Range("A1:BJ1")
'Loop through all the column headers in the all data tab
For Each ws1_xlCell In ws1_xlRange
    valueToFind = ws1_xlCell.Value
        'Loop for - Refined event data tab
        'check whether column headers match. If so, paste column from event tab to relevant column in all data tab
        For Each ws2_xlCell In ws2_xlRange
            If ws2_xlCell.Value = valueToFind Then
                ws2_xlCell.EntireColumn.Copy
                ws1_xlCell.PasteSpecial xlPasteValuesAndNumberFormats
            End If
        Next ws2_xlCell
        'Loop for - Refined MASH data tab
        'check whether column headers match. If so, paste column from MASH tab to relevant column in all data tab
        'ensure data is pasted below data from previous loop and only pulling data with '0' in col O, 'N' in col Q in MASH tab
         For Each ws3_xlCell In ws3_xlRange
            If ws3_xlCell.Value = valueToFind Then
                ws3_xlCell.End(xlUp).Copy
                lastrow = ws1.Cells(rows.Count,"..." ).End(xlUp).Row + 1
                Range("..." & lastRow).Select    
                Selection.PasteSpecial xlPasteValuesAndNumberFormats
            End If
        Next ws3_xlCell

Next ws1_xlCell
End Sub

问题是通过第二个数据表（WS3）循环时出现的。我需要将这些列粘贴在主表中相关列的下一行。我打算使用lastrow维度来计算这一点，但需要一个行/列来定义它。不幸的是，列（"..."）被绑在ws1_xlCell维度上，该维度随着循环的处理而移动。

是否可以从定义为维度的单元格中提取列参考？

欢呼，蚂蚁

Answer 1

感谢@sjr-所讨论的代码的完整行是

lastrow = ws1.Cells(Rows.Count, ws1_xlCell.Column).End(xlUp).Row + 1
                    Range(ws1_xlCell.Column & lastrow).Select