Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Repeat vba code for specific array of worksheets

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Repeat vba code for specific array of worksheets

    Hi,

    I'm trying to figure out how have the below code re-run through a certain array of worksheets in the same workbook.

    From the following code I need to loop through an array of worksheet names performing the same vba code each time:

    Code:
     Sheets(Array("Sheet1", "Sheet2")).Select 
    
    For Each worksheet In Array
    
    Next worksheet In an array
    Can anybody help me out with this one please?

    Here is the full code:

    Code:
    Sub test()
        Dim cmonth As Date, cfind As Range, pmonth As Date, nmonth As Date, ws As Worksheet
        With Worksheets("Dashboard")
            cmonth = .Range("J2").Value
            pmonth = .Range("K2").Value
            nmonth = .Range("I2").Value
            
             For Each worksheet In a Set array
                
                ActiveSheet.Cells.EntireColumn.Hidden = False
                
                Set cfind = .Rows("7:7").Find(what:=CDate(cmonth), lookat:=xlWhole)
                If Not cfind Is Nothing Then
                    If cfind.Offset(2, 0).Value <> "" Then
                        Range(cfind.Offset(0, 1), cfind.End(xlToRight)).EntireColumn.Hidden = True
                        MsgBox "This action can't be performed as you will over write the formulas. Please insert the correct current month and previous month in the Dashboard sheet"
                        Exit Sub
                    Else
                    End If
                End If
                
                Set cfind = .Rows("7:7").Find(what:=CDate(pmonth), lookat:=xlWhole)
                If Not cfind Is Nothing Then
                    Range(cfind.Offset(1, 0), cfind.End(xlDown)).Copy
                    Range(cfind.Offset(1, 1), cfind.Offset(1, 1).End(xlDown)).PasteSpecial xlPasteAll
                    cfind.Offset(1, 0).PasteSpecial xlPasteValues
                End If
                
                Range(cfind.Offset(0, 2), cfind.End(xlToRight)).EntireColumn.Hidden = True
            Next worksheet In an array
         End With
        
        Application.CutCopyMode = False
        
    End Sub
    Kind regards,

    05125668

  • #2
    Re: Repeat vba code for specific array of worksheets

    Like this:

    Code:
    Sub test()
        Dim cmonth As Date, cfind As Range, pmonth As Date, nmonth As Date, ws As Worksheet, wsName As String
        With Worksheets("Dashboard")
            cmonth = .Range("J2").Value
            pmonth = .Range("K2").Value
            nmonth = .Range("I2").Value
    
            For Each wsName In Array("sheet1", "sheet2", "sheet3")
    
                Set ws = Worksheets(wsName)
    
                'continue working with ws as the qualifier for the worksheet
    
            Next
        End With
    
        Application.CutCopyMode = False
    
    End Sub
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Comment


    • #3
      Re: Repeat vba code for specific array of worksheets

      Hi,

      Thanks very much for the reply.

      I have tried as you have suggested below, however I receive the following error "Complie Error: For Each control variable must be Variant or Object" and the "wsName" text is highlighted.

      Code:
      Sub test()
          Dim cmonth As Date, cfind As Range, pmonth As Date, nmonth As Date, ws As Worksheet, wsName As String
          With Worksheets("Dashboard")
              cmonth = .Range("J2").Value
              pmonth = .Range("K2").Value
              nmonth = .Range("I2").Value
           
              For Each wsName In Array("Sheet1", "Sheet2")
              Set ws = Worksheets(wsName)
      I have tried changing the wsName to Variant and Object however I get the following message when this is done "Run-time error "9": Subscript out of range"

      Code:
      Sub test()
          Dim cmonth As Date, cfind As Range, pmonth As Date, nmonth As Date, ws As Worksheet, wsName As Variant
          With Worksheets("Dashboard")
              cmonth = .Range("J2").Value
              pmonth = .Range("K2").Value
              nmonth = .Range("I2").Value
           
              For Each wsName In Array("Sheet1", "Sheet2")
              Set ws = Worksheets(wsName)
      Do you know why I will be receiving these error messages?

      Kind regards,

      Comment


      • #4
        Re: Repeat vba code for specific array of worksheets

        Code:
        sub test
        
        dim wksht as worksheet
        
        for each wksht in worksheets(Array("sheet1", ...)
        
        ...
        
        next wksht
        
        end Sub

        Comment


        • #5
          Re: Repeat vba code for specific array of worksheets

          Apologies, it should have read:

          Code:
          Dim wsName As Variant
          Regards,

          Wigi

          Excel MVP 2011-2014

          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

          Comment


          • #6
            Re: Repeat vba code for specific array of worksheets

            I still seem to get the "Run-time error '9': Subscript out of range" error when modifying the code as you mentioned. Do you have any other ideas on this?


            Code:
            Sub test()
                Dim cmonth As Date, cfind As Range, pmonth As Date, nmonth As Date, ws As Worksheet, wsName As Variant
                With Worksheets("Dashboard")
                    cmonth = .Range("J2").Value
                    pmonth = .Range("K2").Value
                    nmonth = .Range("I2").Value
                 
                    For Each wsName In Worksheets(Array("Sheet1", "Sheet2"))
                    Set ws = Worksheets(wsName)
                    
                    ActiveSheet.Cells.EntireColumn.Hidden = False
                    
                        Set cfind = .Rows("7:7").Find(what:=CDate(cmonth), lookat:=xlWhole)
                        If Not cfind Is Nothing Then
                            If cfind.Offset(2, 0).Value <> "" Then
                            Range(cfind.Offset(0, 1), cfind.End(xlToRight)).EntireColumn.Hidden = True
                            MsgBox "This action can't be performed as you will over write the formulas.  Please insert the correct current month and previous month in the Dashboard sheet"
                            Exit Sub
                            Else
                            End If
                        End If
                    
                        Set cfind = .Rows("7:7").Find(what:=CDate(pmonth), lookat:=xlWhole)
                        If Not cfind Is Nothing Then
                            Range(cfind.Offset(1, 0), cfind.End(xlDown)).Copy
                            Range(cfind.Offset(1, 1), cfind.Offset(1, 1).End(xlDown)).PasteSpecial xlPasteAll
                            cfind.Offset(1, 0).PasteSpecial xlPasteValues
                        End If
                        
                        Range(cfind.Offset(0, 2), cfind.End(xlToRight)).EntireColumn.Hidden = True
                    Next wsName
                End With
                
                Application.CutCopyMode = False
                 
            End Sub
            Thanks again for looking at this.

            Comment


            • #7
              Re: Repeat vba code for specific array of worksheets

              The change in my previous post is the ONLY change you had to do.

              Specifically, your array of worksheets is incorrect. Please look at my first code again, with the change I suggested.
              Regards,

              Wigi

              Excel MVP 2011-2014

              For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

              -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X