Hi,
I'm trying to figure out how have the below code re-run through a certain array of worksheets in the same workbook.
I have highlighted the area in the code where there need the code to perform a set of tasks for each worksheet listed in an array.
Can anybody help me out with this one please?
Here is the 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