Repeat vba code for specific array of worksheets

05125668 · Feb 1st 2012

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

Display More

Kind regards,

05125668

Wigi · Feb 1st 2012

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

Display More

05125668 · Feb 4th 2012

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,

kel.ley · Feb 4th 2012

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

Display More

Wigi · Feb 4th 2012

Re: Repeat vba code for specific array of worksheets

Apologies, it should have read:

Code

Dim wsName As Variant

05125668 · Feb 4th 2012

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

Display More

Thanks again for looking at this.

Wigi · Feb 4th 2012

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.

Repeat vba code for specific array of worksheets

Participate now!

Share