Help with Array

Mr Denove

Active Member
Joined
Jun 8, 2007
Messages
430
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.

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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
Just make sure that the sheets name in your array are correct Mind the extra spaces as well
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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.
VBA Code:
For Each Sh In ThisWorkbook.Sheets(Array("ALP Internal", "ALP Internal Closed", "External", "External Closed", "Non Trading", "Non Trading Closed"))
 
Solution

Mr Denove

Active Member
Joined
Jun 8, 2007
Messages
430
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

Active Member
Joined
Jun 8, 2007
Messages
430
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top