Apr 19 2019 05:57 AM
Hello,
I am new to writing macros and had some help on the one I have. How can i stop the macro below from running in all sheets except one? It autoruns when the the worksheet is open and if you open in the wrong tab it will run in that tab. Please let me know what you think.
Code in Sheet 1 ( the sheet i need to update)
Private Sub Workbook_Open()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Hoja1") 'change this to the sheetname of your report
ws.Activate
Call HideColumns
End Sub
Module Code
Public Sub HideColumns()
Dim wb As Workbook
Dim ws As Worksheet
Dim rngDates As Range
Dim datToday As Date
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet 'run this macro from the sheet you want the hidden columns
Set rngDates = ws.Range("L8:AZ8") 'this range should match the range of dates
Application.ScreenUpdating = False
'make sure you unhide all columns before hiding them based on dates
rngDates.Select
Selection.EntireColumn.Hidden = False
datToday = Date
For Each cell In rngDates.Cells
If cell.Value <= (datToday - 7) Or cell.Value > (datToday + 84) Then
cell.Select
Selection.EntireColumn.Hidden = True
End If
Next
ws.Range("A1").Select
Application.ScreenUpdating = True
'MsgBox Done!, vbOKOnly
End Sub
Thank you!