Switch between Workbooks, Loop through sheets and copy values-CodePudding

I'm stuck, I am trying to write the macro, we have 2 files; ORG - empty file that I am running the macro from NOT_ORG - sheets with some data I want to loop through sheets in NOT_Org, check if this sheet name is in array, if so, create a new sheet in ORG file, paste the value from NOT_ORG, and loop over next sheets from NOT_ORG to check the same. Below code I wrote so far, I am getting confused how to comfortable switch between Workbooks, as I am getting errors - either when I am setting WB2 and second issue is within For each loop. Could someone point me to the right direction, how to switch between these workbooks within this for each loop?

Sub Test1()

  Dim WshtNames As Variant
  Dim WshtNameCrnt As Variant
  Dim ws As Worksheet
  Dim os As Worksheet
  Dim x As String
  Dim WB1 As Workbook
  Dim WB2 As Workbook
  Set WB1 = ActiveWorkbook
  Set WB2 = Workbooks.Open("C:\NOT_ORG.xlsx")

  ' loop through sheets in file that i just open (wb2), check if sheet name is in array below, 
  ' copy A1 value & assign to a variable sheet name that i am copying from, with new prefix 
  ' _new / open "org file" - that I am running macro from - create a new sheet and assign this 
  ' sheet name, paste the value. And do the same for each sheet if this sheet name is in list.
  '
  WshtNames = Array("2", "3")

  For Each WshtNameCrnt In WshtNames
    With Worksheets(WshtNameCrnt).Activate
      Range("A1").Select
      Selection.Copy
      Sheets.Add.Name = WshtNameCrnt & "_new"
      ' -> S
      
      'x = ActiveSheet.Name
      '#MsgBox (x)
      ActiveSheet.Paste
          
    End With
  Next WshtNameCrnt

End Sub

Thanks eM

CodePudding user response：

Please, replace:

For Each WshtNameCrnt In WshtNames
    With Worksheets(WshtNameCrnt).Activate
      Range("A1").Select
      Selection.Copy
      Sheets.Add.Name = WshtNameCrnt & "_new"
      ' -> S
      
      'x = ActiveSheet.Name
      '#MsgBox (x)
      ActiveSheet.Paste
          
    End With
  Next WshtNameCrnt

with:

  For Each WshtNameCrnt In WshtNames
    WB1.Sheets.Add.Name = WshtNameCrnt & "_new"
    WB2.Worksheets(WshtNameCrnt).Range("A1").Copy ActiveSheet.Range("A1")
  Next WshtNameCrnt

In order to work, two sheets named as "1" and "2" (not their index) should exist in WB2.

Activating, selecting only consumes Excel resources, making the code slower and not bringing any benefit...