主页 / coding / 问题 / 79239759
Accepted
Ramadan Moussa
Asked: 2024-11-30 22:34:10 +0800 CST

使用 VBA 在两个不同的工作簿之间复制

  • 772

我得到了一个代码，用于将数据从已关闭的工作簿复制到另一个活动的工作簿，但是在运行该代码后关闭工作簿时遇到了问题，我不知道如何修复它

我的源工作簿是“停止工作”sheet1，目标工作簿是“AUTHS”，工作表名称是“停止工作”，代码运行顺利，但我的问题是，在运行代码并复制数据后，我正在处理的活动工作簿“AUTHS”被关闭，而关闭的源工作簿被打开，而我需要反之亦然（保存并关闭“停止工作”并保持“AUTHS”打开 - 我试过改变真假但什么也没做这是我的代码

Sub mycode()

Workbooks.Open Filename:="D:\Desktop\Stop Work.xlsm"
Worksheets("Sheet1").Cells.Select
Selection.Copy

Workbooks.Open Filename:="D:\Desktop\AUTHs.xlsm"
Worksheets("Stop Work").Cells.Select
Selection.PasteSpecial xlPasteAll             'xlPasteAll to paste everything
ActiveWorkbook.Save

ThisWorkbook.Close SaveChanges:=False        'to close the file
Workbooks("D:\Desktop\Stop Work.xlsm").Close SaveChanges:=True  'to close the file

End Sub



thank you in advance
excel

2 个回答

  1. Best Answer

    最好使用变量来标识工作簿。如下所示

    ' Open the source workbook  
Set sourceWorkbook = Workbooks.Open(Filename:="D:\Desktop\Stop Work.xlsm")  

sourceWorkbook.Worksheets("Sheet1").Cells.Copy  

' Open the destination workbook  
Set destinationWorkbook = Workbooks.Open(Filename:="D:\Desktop\AUTHs.xlsm")  
 
destinationWorkbook.Worksheets("Stop Work").Cells.PasteSpecial xlPasteAll  

destinationWorkbook.Save  

' Close the source workbook after saving changes  
sourceWorkbook.Close SaveChanges:=False
    • 0

  2. 从封闭的工作簿中复制

    Sub CopyFromClosedWorkbook()
    
    ' Reference the source objects.
    Dim swb As Workbook: Set swb = Workbooks.Open("D:\Desktop\Stop Work.xlsm")
    Dim sws As Worksheet: Set sws = swb.Sheets("Sheet1")
    Dim srg As Range:
    With sws.UsedRange
        Set srg = sws.Range("A1", .Cells(.Cells.CountLarge))
    End With
    
    ' Reference the destination objects.
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = dwb.Sheets("Stop Work")
    Dim dfcell As Range
    ' Either overwrite...
    dws.UsedRange.Clear ' clear existing data
    Set dfcell = dws.Range("A1")
    ' ... or append:
    'With dws.UsedRange
    '    Set dfcell = .Cells(1).EntireRow.Columns("A").Offset(.Rows.Count)
    'End With
    Dim drg As Range: Set drg = dfcell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    ' Either copy values, formulas and formatting...
    srg.Copy Destination:=drg
    ' ... or copy only values:
    'drg.Value = srg.Value
    
    ' Close the source workbook.
    swb.Close SaveChanges:=False ' just read from
    
    ' Save the destination workbook.
    dwb.Save
    
    ' Inform.
    MsgBox "Data copied.", vbInformation

End Sub
    • 0

相关问题