RunTime-Error 424; Trying to DIM a named (found in multiple workbooks) Worksheet

Question

I have a monthly report, so I have a template that I update each month, then run code to update dozens of workbooks. I want to set the specific worksheet that needs sorted, so that I only need to change 1 line of code each month (not dozens).

I have successfully set each of my workbooks as WB1 (the template), WB2, WB3, etc. and each workbook has the same worksheets (Jan, Feb, Mar, etc.). My code will copy what I do in the template, paste it into each workbook, update their pivot tables, then sort the results on the monthly sheets.

'Dim March worksheet?

Dim WS As Worksheets
    Set WS = Workbook.Sheets("Mar")

'sort for worksheets
'Macro3 Macro


wb2.WS.Sort.SortFields.Add Key:=Range("C3:C190" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With wb2.WS.Sort
        .SetRange Range("B2:E189")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        .SetRange Range("B191:E8040")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I want to be able to change the dim (i.e. to "APR" for next month), as I have many workbooks that need sorted on that sheet. Currently, if I dont try to Dim the worksheet and just replace WS with Worksheets("Mar") it works, but this will require dozens of changes to "APR", "May", and so on. I want to only change the "Mar" in the line that I set the variable.

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2019-04-12T20:00:06

Dim WS As Worksheets is part of the problem. You want it As Worksheet (no "s", it's one sheet, not a collection of sheets). Then you try to assign it:

Set WS = Workbook.Sheets("Mar")

We don't know what Workbook is, but assuming it's holding an valid object reference to the Workbook object you mean to work with, you'll want to dereference the sheet from the Worksheets collection to avoid surprises later: Sheets can contain Worksheet objects, but also Chart items, and half a dozen other non-worksheet legacy sheet types that don't have a Range member.

wb2.WS.Sort....

Assuming wb2 already holds a reference to the Workbook object you mean to work with (?), it's a Workbook object exposing the members of the Workbook interface, which doesn't include any WS member. If WS is the Worksheet object declared & assigned above, then you don't need to qualify it: like a Range knows what Worksheet it belongs to, a Worksheet knows what Workbook is belongs to.

Did you mean to work with WS directly?

WS.Sort.....

Further down:

.SetRange Range("B2:E189")

That unqualified Range is implicitly referring to whatever worksheet is currently the ActiveSheet - probably not what you mean to do. Should that be WS.Range?

RunTime-Error 424; Trying to DIM a named (found in multiple workbooks) Worksheet

1 Answers