VBA - Subscript out of range

[ExcelMercy]

Right at: Set ws11 = ThisWorkbook.Worksheets("Market_Totals")


I'm getting Subscript out of range... any ideas?

Sub Market_Confirm_Test()
    
    Dim ws11       As Worksheet
    Dim ws12       As Worksheet
    Dim x          As Long
    Dim y          As Long
    Dim SystemCode As Variant
    
    Application.ScreenUpdating = False

    Set ws11 = ThisWorkbook.Worksheets("Market_Totals")
    Set ws12 = Worksheets.Add
    
    SystemCode = Array("AP_123_Lo_4", "AP_123_Lo_6", "JF_123_Lo_1_SYS", "JF_123_Lo_2", "HG_123_Lo_2_SYS")
    
    With ws12
        .Name = "Market_Confirm"
        .Move after:=Sheets(Sheets.Count)
        .Range("A1").Resize(1, 7).Value = Array("System Code", "First Name", "Last Name", "Address 1", "City", "State", "Market ID")
    End With
    
    With ws11
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Range("D" & .Rows.Count).End(xlUp).Row
        y = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1

         .Cells(1, "D").Resize(x).AutoFilter Field:=1, Criteria1:=SystemCode, Operator:=xlFilterValues
        If .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
            .Range("D2").Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
            ws12.Range("A2").PasteSpecial xlPasteAll
            .Range("F2").Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
            ws12.Range("B2").PasteSpecial xlPasteAll
            .Range("H2").Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
            ws12.Range("C2").PasteSpecial xlPasteAll
            .Range("I2").Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
            ws12.Range("D2").PasteSpecial xlPasteAll
            .Range("K2").Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
            ws12.Range("E2").PasteSpecial xlPasteAll
            .Range("L2").Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
            ws12.Range("F2").PasteSpecial xlPasteAll
            .Range("B2").Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
            ws12.Range("G2").PasteSpecial xlPasteAll
        End If
    End With
    
    Application.ScreenUpdating = True
    Set ws11 = Nothing
    Set ws12 = Nothing

End Sub

Example Data:



Starting Sheet (Market_Totals)

Type

Market ID

Order by

System Code

Name id

First Name

Middle Initial

Last Name

Address 1

Address 2

City

State

Postal code

1

213546

*

AP_123_Lo_4

75473d

Billy

C

Smith

111 N Street

Philadelphia

PA

12345

1

432452

*

AP_123_Lo_5

756859d

Jacob

Johnson

123 S Street

New Orleans

LA

84001

1

3425267

*

AP_123_Lo_6

7646d

Sue

Doe

123 Main St

Atlanta

GA

65431

1

8798567

*

AP_123_Lo_7

435322fg

Becky

A

Smith

123 NorthWest Main Rd

Nashville

TN

45678

2

679732542

*

AP_123_Lo_8

4325253fg

Stacy

Marshall

9483 Walkway Dr

Houston

TX

54634

2

3242368

*

JF_123_Lo_1_SYS

23215fg

Larence

S

Donald

2143 Systems Avn

New Orleans

LA

84001

1

6775674

*

JF_123_Lo_2

64345d

Kimberly

Jones

123 Timber Rd

Nashville

TN

54001

1

53424567

*

JF_123_Lo_2

6788900d

Mike

G

Gareld

136 South rd

Philadelphia

PA

45201

1

8798567

*

HG_123_Lo_1_SYS

6422fg

Becky

A

Smith

788 Landing Rd

Nashville

TN

45678

2

679732542

*

HG_123_Lo_1_SYS

6233fg

Stacy

Marshall

3 Moore Dr

Philadelphia

PA

85201

2

3242368

*

HG_123_Lo_1_SYS

5234fg

Larence

S

Donald

212 Lake Drive

Philadelphia

PA

95201

1

6775674

*

HG_123_Lo_2_SYS

3125d

Kimberly

Jones

1677 Trees Rd

New Orleans

LA

84001

1

53424567

*

HG_123_Lo_2_SYS

432656d

Mike

G

Gareld

13455 Northsouth Rd

Philadelphia

PA

65201

<tbody>
</tbody>

Output:

System Code	First Name	Last Name	Address 1	City	State	Market ID
AP_123_Lo_4	Billy	Smith	111 N Street	Philadelphia	PA	213546
AP_123_Lo_6	Jacob	Johnson	123 S Street	New Orleans	LA	432452
JF_123_Lo_1_SYS	Larence	Donald	2143 Systems Avn	New Orleans	LA	3242368
JF_123_Lo_2	Kimberly	Jones	123 Timber Rd	Nashville	TN	6775674
HG_123_Lo_2_SYS	Kimberly	Jones	1677 Trees Rd	New Orleans	LA	6775674
HG_123_Lo_2_SYS	Mike	Gareld	13455 Northsouth Rd	Philadelphia	PA	53424567

<tbody>
</tbody>

 

[Andrew Poulsom]

The error means that there isn't a worksheet named Market_Totals in the workbook that contains your code (ThisWorkbook). Check the spelling thoroughly (including leading or trailing spaces) or maybe the sheet is in another open workbook.

 

[ExcelMercy]

If the sheet was called "Market Totals" and I had it as Set ws11 = ThisWorkbook.Worksheets("Market Totals") would that cause any issues? Like does it need to be a whole word or is the space fine?

 

[Andrew Poulsom]

The name must match exactly so it should include the space.