Help with Array

[Mr Denove]

Morning all, need a bit of help fixing my array that in theory should be simple but I simply cant get to work. The workbook 'Legal Updates' contains the code, it opens another workbook 'Legal New Build Draft' and compiles the 6 specific sheets into one sheet, each entry appearing after the last one. Can someone please advise where I am going wrong with this please? Im getting Run-time error 9 at 'For Each Sh in Sheets....
As always thanks in advance.

Sub Legal_Combined_Array()

Dim WB2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet

Dim myArray As Worksheet
Dim Sh As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayStatusBar = False
ActiveSheet.DisplayPageBreaks = False
Application.CutCopyMode = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.AutomationSecurity = msoAutomationSecurityForceDisable



Set WB2 = Workbooks.Open(Filename:="X:\Folder\Legal Tracker\Legal New Build New Draft.xlsm", _
    ReadOnly:=True)

Set WS1 = ThisWorkbook.Sheets("Legal Data")

WS1.Rows("2:" & WS1.Rows.Count).ClearContents



For Each Sh In Sheets(Array("ALP Internal", "ALP Internal Closed", "External", "External Closed", "Non Trading", "Non Trading Closed"))
 
Dim LastRow As Long
With Sh
LastRow = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).Row
End With

MsgBox "LastRow"

Next Sh

WB2.Close SaveChanges:=False


    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.DisplayStatusBar = True
ActiveSheet.DisplayPageBreaks = False
Application.CutCopyMode = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.AutomationSecurity = msoAutomationSecurityLow




End Sub

 

[mohadin]

Hi
Just make sure that the sheets name in your array are correct Mind the extra spaces as well

 

[Norie]

Are 'ALP Internal', 'ALP Internal Closed' etc. in the workbook you've opened or the workbook the code is in?

If it's the former you need to prefix Sheets with WB2.

For Each Sh In WB2.Sheets(Array("ALP Internal", "ALP Internal Closed", "External", "External Closed", "Non Trading", "Non Trading Closed"))

If it's the latter the prefix would be ThisWorkbook.

For Each Sh In ThisWorkbook.Sheets(Array("ALP Internal", "ALP Internal Closed", "External", "External Closed", "Non Trading", "Non Trading Closed"))

 

[Mr Denove]

Thanks both.
Norie - in response I have added the WB2.Sheets(Array etc but Im afraid its still giving me Runtime Error 9.
The sheets are being compiled from the newly opened workbook (source workbook if you like).

 

[Mr Denove]

Aah, just noticed there are 2 spaces between ALP Internal in the ALP Internal Closed workbook. Looks like a combination of specifying the workbook AND more importantly naming the worksheets correctly.
Thanks both code now compiling.
Very much appreciated.