Copy and paste specific cells between Workbooks

[Dave T]

Hello All,

I have been using the following macro to copy various columns to between two workbooks.
The problem I have is that it copies data from every worksheet in the source workbook. The macro copies specific data from the source worksheet - Sheet1 and then copies data from the same range in Sheet2, and so on for each worksheet in the source workbook.
Can someone please edit the macro so that only the data on Sheet1 from the Test - v1 (2023-03-27).xlsm workbook (source) is copied to Sheet1 of the Test (no macros) - v1.xlsx workbook (destination).

Sub CopyData()
  'https://www.mrexcel.com/board/threads/copy-and-paste-specific-cells-between-workbooks-based-on-criteria.1195526/
  
  'Both Workbooks MUST be open when running the macro.
  
  Dim wb1 As Workbook
  Dim wb2 As Workbook
  Dim ws As Worksheet
  Dim ws1 As Worksheet
  Dim cel As Range
  Dim MyRng As Range
  Dim i As Long
  Dim lrow As Long
  
  Set wb1 = Workbooks("Test - v1 (2023-03-27).xlsm")
  
  Set wb2 = Workbooks("Test (no macros) - v1.xlsx")
  
  Set ws1 = wb2.Sheets("Sheet1")
  
  i = 3
  
  For Each ws In wb1.Worksheets
    lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    Set MyRng = ws.Range(ws.Cells(3, 1), ws.Cells(lrow, 1))
    
    For Each cel In MyRng
      If cel <> "" Then
        ws1.Range("A" & i) = cel
        ws1.Range("B" & i) = cel.Offset(0, 1)
        'ws1.Range("C" & i) = cel.Offset(0, 2)  'Column C not included as this is a formula
        ws1.Range("D" & i) = cel.Offset(0, 3)
        ws1.Range("E" & i) = cel.Offset(0, 4)
        ws1.Range("F" & i) = cel.Offset(0, 5)
        ws1.Range("G" & i) = cel.Offset(0, 6)
        ws1.Range("H" & i) = cel.Offset(0, 7)
        ws1.Range("I" & i) = cel.Offset(0, 8)
        ws1.Range("J" & i) = cel.Offset(0, 9)
        ws1.Range("K" & i) = cel.Offset(0, 10)
        i = i + 1
      Else
      End If
    Next cel
  Next ws
End Sub

I know it is associated with the 'For Each ws' part of the macro (worksheet loop), but I cannot work out how to remove this.
As there is no need to go through each worksheet, what needs to be done for the code to work on only one sheet from the source workbook and only one sheet in the destination workbook?


I have attached two workbook, I have been using whilst testing this macro.

Any help appreciated.
Regards, Dave T