将数据从一个工作表复制到另一个工作表

Question

我有处理和消除质谱数据的代码(这是可行的)。

我有另一个命令从工作表中复制数据，并将其粘贴到宏(Sheet1)的同一个工作表中。它将一行代码粘贴到工作表上，而不是proteinGroups中的信息。

Set wb = Workbooks.Open("C:\Users\X241066\Downloads\PGroupTest.xlsm")
myFile = "C:\Users\X241066\Desktop\Pgroup\proteinGroups.xls"
Workbooks.Open myFile
Worksheets("proteinGroups").Copy
Workbooks("ProteinGroups.xls").Close SaveChanges:=True
wb.Activate
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("E1")
Application.CutCopyMode = False

我尝试的每一次命令迭代都会将数据粘贴到一个新的随机工作簿中。

Answer 1

将UsedRange复制到另一个工作簿

• Worksheet.UsedRange property

• Range.Copy method

• On Worksheet.Copy method page，声明如下：

如果没有指定之前或之后，Microsoft将创建一个新的工作簿，其中包含复制的工作表对象.。

Option Explicit

Sub CopyProteinGroups()
    
    ' Source (Copy FROM (Read))
    Dim swb As Workbook
    Set swb = Workbooks.Open("C:\Users\X241066\Desktop\Pgroup\proteinGroups.xls")
    Dim sws As Worksheet: Set sws = swb.Worksheets("ProteinGroups")
    Dim srg As Range: Set srg = sws.UsedRange
    
    ' Destination (Copy TO (Write))
    Dim dwb As Workbook
    Set dwb = Workbooks.Open("C:\Users\X241066\Downloads\PGroupTest.xlsm")
    Dim dws As Worksheet: Set dws = dwb.Worksheets("Sheet1")
    Dim dfCell As Range: Set dfCell = dws.Range("E1")
    
    ' Copy
    srg.Copy dfCell
    
    ' Save and/or Close
    swb.Close SaveChanges:=False ' no need to save; it was only read from
    
    ' dwb.Close SaveChanges:=True ' save when you're done; it was written to
    
End Sub