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.
As always thanks in advance.
VBA Code:
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