locked
Refer to Worksheet Code Number in a For Next loop RRS feed

  • Question

  • Using Windows 10 and MS Office 16 (Office 365)

    I wish to refer in code to worksheets by the sheet code number (not the index number) so that I can step through worksheets in a For Next loop.  I have a workbook with three worksheets in the order (as seen in the VBA explorer window) Sheet1(TEST_C), Sheet2(TEST_B), Sheet3(TEST_A).  However, the order of the worksheet tabs in the  workbook is Sheet3(TEST_A), Sheet2(TEST_B), Sheet 1(TEST_C).  So the index number of Sheet1 is 3, Sheet2 is 2 and Sheet3 is 1. I wish to select in turn TEST_A (Sheet3), and then TEST_B (Sheet2).  I can do it by referring to the index number (TEST_A is sheet index number 1, but is sheet code number 3), but if I change the order of the tabs later on, I then have to rewrite my code to cater for the new tab order.  If I put into the Immediate Window  Sheet3.Select,  TEST_A is correctly selected, so I want to refer in code to Sheet3, then Sheet2 in turn.

    This is the code I would like to write, but I can't define the variable in line 5:

    Sub SelectSheets()
    Dim intCounter As Integer

        For intCounter = 3 To 2 Step -1
            DEFINE THE VARIABLE.Select
        Next intCounter

    End Sub

      I have tried "Worksheets(intCounter).Select" in line 5, but that steps me through in reverse tab order, so I get TEST_C (Sheet1) then TEST_B (Sheet2) because it reads the intCounter as an Index number.  I have tried various combinations of "Sheet &  intcounter" and so on without success.

    Any guidance on how to refer to the worksheet code numbers using a variable will be much appreciated.

    Andy C

    Tuesday, February 2, 2016 5:45 PM

Answers

  • Hi Andy,

    Based on the CodeName to get the worksheet, you could refer to this code below:

    Dim s As String
    Dim ws As Worksheet
    s = ActiveWorkbook.VBProject.VBComponents("Sheet4").Properties("Name").value
    Set ws = Worksheets(s)

    So, if the CodeName of worksheets format like Sheet1, Sheet2, Sheet3…, you could have a loop according to the count of the worksheet and combine the Sheet string and current index, then get the worksheet object.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 3, 2016 8:05 AM
  • Starain

    Thanks for your suggestion.  A neat approach, but I still have to find a way of referencing each Sheet code number in the loop in your line s = ActiveWorkbook.VBProject.VBComponents("Sheet4").Properties("Name").value

    So I still would need to find the syntax using my "intCounter" variable that would give Sheet1 when intCounter = 1.

    It begins to look as if it is not possible to do this in VBA, so I will have to use another approach, probably using the worksheet index number and accepting that the tabs have to remain in the original order.

    Andy C

    • Marked as answer by AndyColRomsey Thursday, February 4, 2016 11:15 AM
    Wednesday, February 3, 2016 9:22 AM

All replies

  • Hi

    Is this what you want ?

    Sub SelectSheets()
     Dim intCounter As Integer

        For intCounter = 3 To 1 Step -1
            Worksheets("sheet" & intCounter).Select
         Next intCounter

    End Sub

    Edit:

    With your Sheets named try this one.

    Sub SelectSheets()
     Dim intCounter As Integer

        For intCounter = 3 To 2 Step -1
           Sheets(intCounter).Select
         Next intCounter

    End Sub


    Cimjet



    • Edited by Cimjet Tuesday, February 2, 2016 6:19 PM
    Tuesday, February 2, 2016 5:56 PM
  • Cimjet

    Thanks for looking at this.  Regrettably your suggestion gives me Runtime error 9 Subscript out of Range.  I  had tried that and thought it should work, but alas, not.  Putting into the immediate window Sheet3.Select does work, so it is getting the variable to equal "Sheet3" that is the difficulty!

    Andy C

    Tuesday, February 2, 2016 6:10 PM
  • Cimjet

    Thanks for your edited second suggestion.  That takes me back to the original problem in that it selects the sheets by the index number rather than by the code number. So it selects sheet index number 3 (i.e. sheet code number 1, TEST_C) first, and I want to get sheet index number 1 (sheet code number 3 , TEST_A) first.  It's simple enough to step through using the index numbers, but referring to the sheet by its index number in the loop is the problem.

    Andy C

    Tuesday, February 2, 2016 6:53 PM
  • Hi

    Try something for me. The index number has to do with the tab position.

    Move the order of the tabs and try the macro again. The one using the Index.

    Sub SelectSheets()
     Dim intCounter As Integer

        For intCounter = 3 To 1 Step -1
           Sheets(intCounter).Select
         Next intCounter

    Try it with a new workbook and 3 sheets

    Open the vba editor and your sheet side by side then press F8 so you can go to each sheets. By changing the order of the tabs you shouls be able to get what you want. If I understand you correctly.

    End Sub


    Cimjet



    • Edited by Cimjet Tuesday, February 2, 2016 7:28 PM
    Tuesday, February 2, 2016 7:23 PM
  • Cimjet

    Thanks again for looking at this.  The problem with the answer you suggested is that it refers to the sheet index rather than the sheet code.  Doing as you suggest would indeed go through the tabs in the right order, but I am trying to write the code so that the individual sheets are selected in the right order, no matter where the tabs are positioned. So when I want to select what appears in the VBA explorer window as "Sheet3(TEST_A)" it does not matter where the tab appears on the worksheet itself. 

    I realise that I can achieve the end result in various ways, by reordering the tab positions, or by referring to each worksheet in turn by name in the code.  Neither is a problem in a small workbook with just 3 worksheets, but in a larger workbook that has many worksheets that could be on tabs in various positions I would like to be able to use a For Next loop to step through a selection of the worksheets in order.

    It may be that it is not possible in VBA to achieve that by using my intCounter variable to refer to the number in "Sheet3", "Sheet2" etc, but I was hoping that there was suitable syntax to make that reference.

    Andy C

    Tuesday, February 2, 2016 10:23 PM
  • Hi Andy,

    Based on the CodeName to get the worksheet, you could refer to this code below:

    Dim s As String
    Dim ws As Worksheet
    s = ActiveWorkbook.VBProject.VBComponents("Sheet4").Properties("Name").value
    Set ws = Worksheets(s)

    So, if the CodeName of worksheets format like Sheet1, Sheet2, Sheet3…, you could have a loop according to the count of the worksheet and combine the Sheet string and current index, then get the worksheet object.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 3, 2016 8:05 AM
  • Starain

    Thanks for your suggestion.  A neat approach, but I still have to find a way of referencing each Sheet code number in the loop in your line s = ActiveWorkbook.VBProject.VBComponents("Sheet4").Properties("Name").value

    So I still would need to find the syntax using my "intCounter" variable that would give Sheet1 when intCounter = 1.

    It begins to look as if it is not possible to do this in VBA, so I will have to use another approach, probably using the worksheet index number and accepting that the tabs have to remain in the original order.

    Andy C

    • Marked as answer by AndyColRomsey Thursday, February 4, 2016 11:15 AM
    Wednesday, February 3, 2016 9:22 AM
  • Let me restate to see if I understand the issue.

    1. You need to process tabs in alphabetic or some other order known to you.
    2. This order is not guaranteed to match the position of the tab; i.e. TESTA may have been moved by the user to the 10th tab from the left but you still want it processed first.
    3. The processing on alpha/special order is mandatory (Love to know why)
    4. The actual sheet index as created by Excel may not match the alpha/special order, i.e. TESTA may be Sheet10

    If that's the case, it sounds like you essentially have a human derived sequencing requirement and you'll just have to define the order (maybe as an array) and process the array in the order you establish.

    Wednesday, February 3, 2016 2:31 PM
  • Dogubob

    That just about sums it up!  Naïve of me, I suppose, but I had hoped that since

    Sheet3.Select

    worked OK, I might be able to replace the "3" with a variable to make

    Sheet intCounter.select

    work in a loop.   But I guess I can't.

    Thanks

    Andy C

    Wednesday, February 3, 2016 4:56 PM
  • Hi Andy,

    >> might be able to replace the "3" with a variable to makeSheet intCounter.select work in a loop.   But I guess I can't.

    I think so.

    >> So I still would need to find the syntax using my "intCounter" variable that would give Sheet1 when intCounter = 1.

    As I said that you could combine the “Sheet” string with intCounter value. (e.g. Sheet, 1=>Sheet1; Sheet,2=>Sheet2)

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 4, 2016 6:17 AM
  • Starain

    Ah, now I understand!  I had missed out the " " from  my code, which is why it did not work.  Now it does.  Many thanks for explaining that

    Andy C

    Thursday, February 4, 2016 11:15 AM