locked
Referencing worksheets by a variable - VBA RRS feed

  • Question

  • Hi Everybody!

    I'm trying to do something by looping through consecutive worksheets with the following code but cannot run it through:

    Sub MultiSheets()
      Dim SheetNum As Integer
      Dim SheetName As String
      Dim i As Integer
     
        SheetNum = 5

          For i = 1 To SheetNum

           SheetName = "Sheet" & i
           Sheets(SheetName).Range(20, 6) = "Go!"

        Next i
    End Sub

    My feeling is that is something missing but I don't know what since I'm very new with VBA.  Help is appreciated.

    Friday, February 14, 2014 5:54 PM

All replies

  • Re:  Sheets and Worksheets
    You can refer to a Sheet by its index number (position) or by its name.

       SheetNum = 5

       For i = 1 To SheetNum
           Sheets(i).Cells(20, 6) = "Go!"
       Next i
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 7:58 PM
    Friday, February 14, 2014 6:08 PM
  • Wow!  Thanks Jim! It is even simpler.  BTW, would be possible to change my code slightly so that the sheet names may be used as well?
    Friday, February 14, 2014 6:26 PM
  • Re:  "change code, so the sheet names may be used"

    Well, then you have to know the names...
    '---
    Dim vShtNames As Variant
    Dim shtNum As Long

     vShtNames = Array("Donald", "Huey", "Dewey", "Louie", "Sheet5")
     For shtNum = LBound(vShtNames) To UBound(vShtNames)
        ThisWorkbook.Sheets(vShtNames(shtNum)).Range("F20").Value = "Test" & shtNum + 1
     Next 'shtNum
    '---

    You may also want to experiment with this...
    ThisWorkbook.Worksheets.FillAcrossSheets Worksheets("Donald").Range("F20")
    '---
    Jim Cone



    • Edited by James Cone Friday, February 14, 2014 7:14 PM
    Friday, February 14, 2014 6:58 PM
  • Thanks lot Jim.  It works with both consecutive and nonconsecutive sheets with more flexibility than using Select Case.

    Best!

    Friday, February 14, 2014 8:29 PM
  • "i" is an integer, you would need to convert it to string first:

    Sub MultiSheets()
        Dim SheetNum As Integer
        Dim SheetName As String
        Dim i As Integer
       
        SheetNum = 5
        For i = 1 To SheetNum
            SheetName = "Sheet" & Strings.Trim(Str(i))
            Sheets(SheetName).Range(20, 6) = "Go!"
        Next i
    End Sub


    VBA Programming Service

    Sunday, February 23, 2014 9:37 AM
  • Thanks Pedrumj2.  Now, I have a sense about this.
    Tuesday, February 25, 2014 7:13 PM