Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Repeat vba code for specific array of worksheets

  1. #1
    Join Date
    23rd July 2010
    Posts
    16

    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:

    VB:
    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:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,006

    Re: Repeat vba code for specific array of worksheets

    Like this:

    VB:
    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, 2012, 2013



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

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

  3. #3
    Join Date
    23rd July 2010
    Posts
    16

    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.

    VB:
    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"

    VB:
    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,

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd January 2012
    Posts
    6

    Re: Repeat vba code for specific array of worksheets

    VB:
    Sub test 
         
        Dim wksht As worksheet 
         
        For Each wksht In worksheets(Array("sheet1", ...) 
             
            ... 
             
        Next wksht 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,006

    Re: Repeat vba code for specific array of worksheets

    Apologies, it should have read:

    VB:
    Dim wsName As Variant 
    
    
    Regards,

    Wigi

    Excel MVP 2011, 2012, 2013



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

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

  6. #6
    Join Date
    23rd July 2010
    Posts
    16

    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?


    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,006

    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, 2012, 2013



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

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Re-run vba code for specific array of worksheets
    By 05125668 in forum EXCEL HELP
    Replies: 1
    Last Post: January 31st, 2012, 23:25
  2. Replies: 9
    Last Post: August 25th, 2011, 07:50
  3. Replies: 9
    Last Post: July 19th, 2011, 00:51
  4. Find and Delete Specific values in specific worksheets
    By DummyinExcel in forum EXCEL HELP
    Replies: 5
    Last Post: March 5th, 2011, 10:09
  5. VBA Code To Unmerge Cells On Specific Worksheets
    By skruber in forum EXCEL HELP
    Replies: 4
    Last Post: January 27th, 2010, 03:08

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno