Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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:

    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

    Excel Video Tutorials / Excel Dashboards Reports


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

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

    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,

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    3rd January 2012
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


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

    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.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?


    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.

    Excel Video Tutorials / Excel Dashboards Reports


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

    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.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: February 1st, 2012, 00:25
  2. Replies: 9
    Last Post: August 25th, 2011, 08:50
  3. Replies: 9
    Last Post: July 19th, 2011, 01:51
  4. Find and Delete Specific values in specific worksheets
    By DummyinExcel in forum EXCEL HELP
    Replies: 5
    Last Post: March 5th, 2011, 11:09
  5. VBA Code To Unmerge Cells On Specific Worksheets
    By skruber in forum EXCEL HELP
    Replies: 4
    Last Post: January 27th, 2010, 04: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