Copying data from one Worksheet to another

Question

I have code where I process and eliminate Mass Spectrometry data (which works).

I have another command to copy that data from its worksheet and to paste it to the same sheet with the Macro (Sheet1). It pastes a line of code to the worksheet instead of the information in 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

Every iteration of commands I tried pastes the data to a new random workbook.

Answer 1

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