用于在名称与范围值相似的打开工作簿中粘贴值的vba代码

Sub PasteValuesInSimilarNamedWorkbook()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim targetWorkbook As Workbook
    Dim targetSheet As Worksheet
    Dim targetRange As Range
    Dim targetWorkbookName As String
    
    ' 设置源工作簿和范围
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    Set sourceRange = ws.Range("A1:B10")
    
    ' 设置目标工作簿名称
    targetWorkbookName = "目标工作簿名称.xlsx"
    
    ' 检查目标工作簿是否已打开，如果已打开则直接使用，否则打开目标工作簿
    On Error Resume Next
    Set targetWorkbook = Workbooks(targetWorkbookName)
    On Error GoTo 0
    
    If targetWorkbook Is Nothing Then
        Set targetWorkbook = Workbooks.Open("目标工作簿路径\" & targetWorkbookName)
    End If
    
    ' 设置目标工作簿和范围
    Set targetSheet = targetWorkbook.Worksheets("Sheet1")
    Set targetRange = targetSheet.Range("A1")
    
    ' 将源范围的值粘贴到目标范围
    targetRange.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value
    
    ' 关闭目标工作簿
    targetWorkbook.Close SaveChanges:=True
    
    ' 清理对象变量
    Set sourceRange = Nothing
    Set targetRange = Nothing
    Set targetSheet = Nothing
    Set targetWorkbook = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub