Home

Stop my macro from running in other sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-474853%22%20slang%3D%22en-US%22%3EStop%20my%20macro%20from%20running%20in%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-474853%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20writing%20macros%20and%20had%20some%20help%20on%20the%20one%20I%20have.%20How%20can%20i%20stop%20the%20macro%20below%20from%20running%20in%20all%20sheets%20except%20one%3F%20It%20autoruns%20when%20the%20the%20worksheet%20is%20open%20and%20if%20you%20open%20in%20the%20wrong%20tab%20it%20will%20run%20in%20that%20tab.%20Please%20let%20me%20know%20what%20you%20think.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CEM%3ECode%20in%20Sheet%201%20(%20the%20sheet%20i%20need%20to%20update)%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EPrivate%20Sub%20Workbook_Open()%3CBR%20%2F%3EDim%20wb%20As%20Workbook%3CBR%20%2F%3EDim%20ws%20As%20Worksheet%3CBR%20%2F%3ESet%20wb%20%3D%20ActiveWorkbook%3CBR%20%2F%3ESet%20ws%20%3D%20wb.Worksheets(%22Hoja1%22)%20'change%20this%20to%20the%20sheetname%20of%20your%20report%3CBR%20%2F%3Ews.Activate%3CBR%20%2F%3ECall%20HideColumns%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3EModule%20Code%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPublic%20Sub%20HideColumns()%3CBR%20%2F%3EDim%20wb%20As%20Workbook%3CBR%20%2F%3EDim%20ws%20As%20Worksheet%3CBR%20%2F%3EDim%20rngDates%20As%20Range%3CBR%20%2F%3EDim%20datToday%20As%20Date%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20wb%20%3D%20ActiveWorkbook%3CBR%20%2F%3ESet%20ws%20%3D%20wb.ActiveSheet%20'run%20this%20macro%20from%20the%20sheet%20you%20want%20the%20hidden%20columns%3CBR%20%2F%3ESet%20rngDates%20%3D%20ws.Range(%22L8%3AAZ8%22)%20'this%20range%20should%20match%20the%20range%20of%20dates%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3E'make%20sure%20you%20unhide%20all%20columns%20before%20hiding%20them%20based%20on%20dates%3CBR%20%2F%3ErngDates.Select%3CBR%20%2F%3ESelection.EntireColumn.Hidden%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EdatToday%20%3D%20Date%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20cell%20In%20rngDates.Cells%3CBR%20%2F%3EIf%20cell.Value%20%26lt%3B%3D%20(datToday%20-%207)%20Or%20cell.Value%20%26gt%3B%20(datToday%20%2B%2084)%20Then%3CBR%20%2F%3Ecell.Select%3CBR%20%2F%3ESelection.EntireColumn.Hidden%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3E%3CBR%20%2F%3Ews.Range(%22A1%22).Select%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3E'MsgBox%20Done!%2C%20vbOKOnly%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-474853%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Tyler Smith
Contributor

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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies