I have a large block of code where I process and eliminate Mass Spectrometry data (which works fine), but then I have another command to copy that data from its worksheet and to paste it to the same sheet with the Macro (Sheet1). Whenever I run the command it pastes a line of code to the worksheet instead of the information in proteinGroups, and every iteration of commands I have tried from google or stackoverflow have just resulted in pasting the data to a new random workbook.

The code in question:

   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

CodePudding user response：

Copy the UsedRange to Another Workbook

• Worksheet.UsedRange property

• Range.Copy method

• On the Worksheet.Copy method page, the following is stated:
  If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied Worksheet object.

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