Using VBA to Loop through Array of Worksheet CodeNames

Question

I am trying to loop through an array of specific worksheets via VBA, but keep getting errors. I've been at this for over a week now, and finally have brought myself to signup here. The code that I currently have is as follows:

Option Explicit
Sub Create_NewEvent2()

Dim i As Variant, wName As Variant, x As Variant, ws As Worksheet
wName = Array("Sheet1", "Sheet3", "Sheet5", "Sheet7", "Sheet9", _
"Sheet13", "Sheet17", "Sheet21", "Sheet23", "Sheet27", "Sheet31", _ 
"Sheet35", "Sheet39", "Sheet43", "Sheet47", "Sheet54", _
"Sheet56", "Sheet57", "Sheet58", "Sheet60", "Sheet61", "Sheet62", _ 
"Sheet63", "Sheet64", "Sheet65", "Sheet82", "Sheet83", "Sheet84", _
 "Sheet85", "Sheet90", "Sheet91", "Sheet93", "Sheet94")


For Each ws In ActiveWorkbook.Worksheets
For i = LBound(wName) To UBound(wName)
If ws.CodeName = wName(i) Then
 ws.Visible = xlSheetVisible
  ws.Range("M7:M38").Select
   Selection.Copy
    ws.Range("D7").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
     Application.CutCopyMode = False
      ws.Range("G7:M38,E7:E38,P43:P45").Select
        ws.Range("P43").Activate
         Selection.ClearContents
          ws.Visible = xlSheetVeryHidden
    Call AutoStock
     End If
 Next i
 Next ws
 End Sub

The error message that I got from this last piece of code is "Select Method of Range Class Failed." When I debug, it has the "ws.Range("M7:M38").Select" highlighted, but I've used this exact syntax in other pieces and worked just fine. Can anyone tell me where I've went wrong with this? Any help will be greatly appreciated..

score 1 · Answer 1 · edited May 23 '17 at 11:52

The Worksheet.CodeName actually returns a Worksheet object. You can construct your array with them directly.

Sub Create_NewEvent2()

    Dim w As Long, vCODENAMEs As Variant

    vCODENAMEs = Array(Sheet1, Sheet3, Sheet5, Sheet7, Sheet9, Sheet13, _
                       Sheet17, Sheet21, Sheet23, Sheet27, Sheet31, Sheet35, _
                       Sheet39, Sheet43, Sheet47, Sheet54, Sheet56, Sheet57, _
                       Sheet58, Sheet60, Sheet61, Sheet62, Sheet63, Sheet64, _
                       Sheet65, Sheet82, Sheet83, Sheet84, Sheet85, Sheet90, _
                       Sheet91, Sheet93, Sheet94)

    For w = LBound(vCODENAMEs) To UBound(vCODENAMEs)
        With vCODENAMEs(w)
            Debug.Print .Name  'string name of the worksheet
            ' all of your other operations here
            '.Visible = xlSheetVisible  'you do not have to unhide for the operations you've chosen if you reference directly
            .Range("D7:D38") = .Range("M7:M38").Value
            .Range("E7:E38,G7:M38,P43:P45").ClearContents
            .Visible = xlSheetVeryHidden
            Call AutoStock
        End With
    Next w

End Sub

With direct worksheet and cell referencing, I've completely avoided all of the .Activate and .Select nonsense. See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

[Glad to hear you got sorted out quickly](http://stackoverflow.com/help/someone-answers). Please take to heart the inferred message that you need to get away from `.Select`. The macro-recorder is a wonderful tool but by the strict definition of its design it does not produce efficient code. — , Jun 07 '15 at 19:24

score 1 · Accepted Answer · answered Jun 07 '15 at 00:56

See revised code with small modifications and comments. I suggest not to use ActiveWorkbook as not necessarily is the one that you want to work with is active. Also there is no need to select the ranges you are working to (comments and documentation referred by @Jeeped)

Sub Create_NewEvent2_New()
Dim wName As Variant, vName As Variant
wName = Array("Sheet1", "Sheet3", "Sheet5", "Sheet7", "Sheet9", _
    "Sheet13", "Sheet17", "Sheet21", "Sheet23", "Sheet27", "Sheet31", _
    "Sheet35", "Sheet39", "Sheet43", "Sheet47", "Sheet54", _
    "Sheet56", "Sheet57", "Sheet58", "Sheet60", "Sheet61", "Sheet62", _
    "Sheet63", "Sheet64", "Sheet65", "Sheet82", "Sheet83", "Sheet84", _
    "Sheet85", "Sheet90", "Sheet91", "Sheet93", "Sheet94")

Dim Wbk As Workbook 'Set workbook variable don't use ActiveWorkbook
Dim ws As Worksheet

    Rem Set Workbook - Change as needed if applied to another workbook.
    Set Wbk = ThisWorkbook

    Rem Loop thru List of Worksheets instead of All Worksheets
    For Each vName In wName

        Set ws = Nothing
        On Error Resume Next
        Set ws = Wbk.Worksheets(vName)
        On Error GoTo 0
        If Not (ws Is Nothing) Then
            With ws
                .Visible = xlSheetVisible
                .Range("M7:M38").Copy
                .Range("D7").PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False
                .Range("G7:M38,E7:E38,P43:P45").ClearContents
                .Visible = xlSheetVeryHidden
            End With
            Call AutoStock

    End If: Next

End Sub

Thanks for the response Jay... I couldn't get this code to do anything though. When I click the button that I assigned it to, nothing at all would happen, so I tried taking out the "If Not" statement, but then got an error msg. However, the response that Jeeped supplied above fixed it for me. — xlJunkie, Jun 07 '15 at 17:12
@xlJunkie thanks for the input. Odd it did work my test. Just for curiosity what sort of error did you get. — EEM, Jun 07 '15 at 19:49
Hey EEM.... I apologize for the delay... I was in the admirals club at DFW when posting this back in June, and have not checked back until now. I cannot remember what error msg I was getting, but I was in a huge rush, so I very well could've messed something up/typo, etc... when I tried it... — xlJunkie, Aug 19 '15 at 23:37
HI @xlJunkie would you kindly accept Jeeped answer, as of now this question still open... — EEM, Oct 20 '15 at 18:19
I am so sorry!! This was my first ever post and did not know that I was supposed to accept and/or how to accept the answer. I just now saw this and accepted.. — xlJunkie, Dec 12 '15 at 23:38

score 0 · Answer 3 · answered Jun 06 '15 at 23:48

0

Select does not work on a worksheet that is not active. Call ws.Activate first and then Select should work.

answered Jun 06 '15 at 23:48

Jay

56,361
10
99
123

Using VBA to Loop through Array of Worksheet CodeNames

3 Answers3