SET WB = WORKBOOKS("") question

[PATSYS]

Hi all,

My code is below.

I am getting error in the line that says "Set WB1 = Workbooks("File1.xlsm")
"

Sub CopyFromFile()

Dim myArray As Variant
Dim sh As Worksheet
Dim WB1 As Workbook
Dim WB2 As Workbook

Set WB1 = Workbooks("File1.xlsm")
Set WB2 = Workbooks("File2.xlsm")

myArray = Array("P&L-A", "P&L-B", "P&L-C", "P&L-D", "P&L-F", "P&L-R")

For Each sh In Sheets(myArray)

    With sh
    
     WB1.sh.Range("AC8:AC12").Value = WB2.sh.Range("AC8:AC12").Value
     WB1.sh.Range("AC14:AC21").Value = WB2.sh.Range("AC14:AC21").Value
     WB1.sh.Range("AC23:AC24").Value = WB2.sh.Range("AC23:AC24").Value
     WB1.sh.Range("AC26:AC28").Value = WB2.sh.Range("AC26:AC28").Value
     WB1.sh.Range("AC30:AC32").Value = WB2.sh.Range("AC30:AC32").Value
     WB1.sh.Range("AC34:AC45").Value = WB2.sh.Range("AC34:AC45").Value
     WB1.sh.Range("AC47:AC48").Value = WB2.sh.Range("AC47:AC48").Value
     WB1.sh.Range("AC50:AC68").Value = WB2.sh.Range("AC50:AC68").Value
     WB1.sh.Range("AC70:AC74").Value = WB2.sh.Range("AC70:AC74").Value
     WB1.sh.Range("AC76:AC80").Value = WB2.sh.Range("AC76:AC80").Value
     WB1.sh.Range("AC83:AC88").Value = WB2.sh.Range("AC83:AC88").Value
     WB1.sh.Range("AC90:AC102").Value = WB2.sh.Range("AC90:AC102").Value
     WB1.sh.Range("AC104:AC110").Value = WB2.sh.Range("AC104:AC110").Value
     WB1.sh.Range("AC112:AC121").Value = WB2.sh.Range("AC112:AC121").Value
     WB1.sh.Range("AC123:AC129").Value = WB2.sh.Range("AC123:AC129").Value
     WB1.sh.Range("AC132:AC144").Value = WB2.sh.Range("AC132:AC144").Value
     WB1.sh.Range("AC146:AC149").Value = WB2.sh.Range("AC146:AC149").Value
     WB1.sh.Range("AC151:AC163").Value = WB2.sh.Range("AC151:AC163").Value
     WB1.sh.Range("AC165:AC170").Value = WB2.sh.Range("AC165:AC170").Value
     WB1.sh.Range("AC172:AC181").Value = WB2.sh.Range("AC172:AC181").Value
     WB1.sh.Range("AC184:AC185").Value = WB2.sh.Range("AC184:AC185").Value
     WB1.sh.Range("AC187:AC191").Value = WB2.sh.Range("AC187:AC191").Value
     WB1.sh.Range("AC194:AC201").Value = WB2.sh.Range("AC194:AC201").Value
     WB1.sh.Range("AC203:AC207").Value = WB2.sh.Range("AC203:AC207").Value
     WB1.sh.Range("AC209:AC217").Value = WB2.sh.Range("AC209:AC217").Value
     WB1.sh.Range("AC219:AC222").Value = WB2.sh.Range("AC219:AC222").Value
     WB1.sh.Range("AC224:AC235").Value = WB2.sh.Range("AC224:AC235").Value
     WB1.sh.Range("AC237:AC250").Value = WB2.sh.Range("AC237:AC250").Value
     WB1.sh.Range("AC254:AC258").Value = WB2.sh.Range("AC254:AC258").Value
    
    End With

Next sh

End Sub

Can somebody show me what the correct code for this?

NOTE: Both File1.xlsm and File2.xlsm are open when I run the macro.

Thanks in advance.

 

[PATSYS]

Correction:

I am getting error on the line that says:

WB1.sh.Range("AC8:AC12").Value = WB2.sh.Range("AC8:AC12").Value

 

[xenou]

Since you have an array of sheet names (strings), I think this syntax will work:

Dim myArray As Variant
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim i As Long

Set WB1 = Workbooks("File1.xlsm")
Set WB2 = Workbooks("File2.xlsm")

myArray = Array("P&L-A", "P&L-B", "P&L-C", "P&L-D", "P&L-F", "P&L-R")

For i = 0 to UBound(myArray)

     WB1.Sheets(i).Range("AC8:AC12").Value = WB2.Sheets(i).Range("AC8:AC12").Value
     '...
     
Next i

 

[PATSYS]

Thanks Xenou.