如何将VBA宏中的固定范围改为动态范围以跨工作簿复制粘贴

方案1：自动识别有效数据范围（无需手动设置命名范围） #

通过获取工作表中最后一行、最后一列的有效数据位置，动态计算复制/清除范围，避免硬编码固定行号列号。修改后的宏代码如下：

Sub Update_Tabs()
    Dim w As Workbook
    Dim x As Workbook
    Dim sourceSheet As Worksheet, targetSheet As Worksheet
    Dim lastSourceRow As Long, lastSourceCol As Long
    Dim targetStartRow As Long
    Dim vals As Variant

    ' 打开工作簿
    Set w = Workbooks.Open("C:\Workbookw.xlsx")
    Set x = Workbooks.Open("G:\Workbookx.xlsx")
    
    ' 定义工作表对象
    Set sourceSheet = x.Sheets("Data")
    Set targetSheet = w.Sheets("Data")
    targetStartRow = 10 ' 目标起始行固定为A10

    ' 获取源数据的最后一行和最后一列
    lastSourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    lastSourceCol = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column

    ' 清除目标工作表对应范围内容
    targetSheet.Range(targetSheet.Cells(targetStartRow, 1), _
                      targetSheet.Cells(targetStartRow + lastSourceRow - 1, lastSourceCol)).ClearContents

    ' 读取源数据并写入目标工作表
    vals = sourceSheet.Range(sourceSheet.Cells(1, 1), _
                            sourceSheet.Cells(lastSourceRow, lastSourceCol)).Value
    targetSheet.Range(targetSheet.Cells(targetStartRow, 1), _
                      targetSheet.Cells(targetStartRow + lastSourceRow - 1, lastSourceCol)).Value = vals

    ' 关闭工作簿并保存修改
    w.Close SaveChanges:=True
    x.Close SaveChanges:=False
End Sub

方案2：使用动态命名范围（解决你之前的失败问题） #

如果坚持用命名范围，需先在Excel中定义动态命名范围（而非固定范围名称）：

1. 打开Workbookx，按Ctrl+F3打开名称管理器，新建名称：
   • 名称：SourceData
   • 引用位置：=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))
     该公式会自动适配A列最后一行、首行最后一列的所有有效数据。
2. 打开Workbookw，同样新建名称：
   • 名称：TargetRange
   • 引用位置：=OFFSET(Data!$A$10,0,0,COUNTA(Workbookx.xlsx!Data!$A:$A),COUNTA(Workbookx.xlsx!Data!$1:$1))
     此设置确保目标范围和源数据的行列数完全匹配。

修改后的宏代码如下：

Sub Update_Tabs()
    Dim w As Workbook
    Dim x As Workbook
    Dim vals As Variant

    Set w = Workbooks.Open("C:\Workbookw.xlsx")
    Set x = Workbooks.Open("G:\Workbookx.xlsx")

    ' 调用动态命名范围操作
    w.Sheets("Data").Range("TargetRange").ClearContents
    vals = x.Sheets("Data").Range("SourceData").Value
    w.Sheets("Data").Range("TargetRange").Value = vals

    w.Close SaveChanges:=True
    x.Close SaveChanges:=False
End Sub

注意：使用动态命名范围时，确保两个工作簿均已打开，或在名称引用中写入完整文件路径（避免路径变动导致失效）。

内容的提问来源于stack exchange，提问作者Bobby