How do I auto refresh my macro?

[jjt1973]

I have created a template that will be used by our national sales team. I have written a macro and will be adding more. This template will need to be able to refresh everytime it is opened so that the macros automatically turn on and refresh with each scenario. Here is the current macro running:

Sub channel()
'This macro will be used to hide row 3 if not active row.

If [E2].Value = "" Then
Range("A3:I3").EntireRow.Hidden = True
End If

If [E2].Value = MN Then
Range("A3:I3").EntireRow.Hidden = True
End If

If [E2].Value = NY Then
Range("A3:I3").EntireRow.Hidden = False
End If

If [E2].Value = WI Then
Range("A3:I3").EntireRow.Hidden = False
End If

End Sub

Thanks.

JJT

 

[VoG]

Try in the ThisWorkbook module

Private Sub Workbook_Open()
Call channel
End Sub

 

[jjt1973]

Do I need to run a similar macro for each individual macro for them all to auto refresh?

 

[VoG]

You would call them one after the other like this

Private Sub Workbook_Open()
Call channel
Call anothermacro
Call macro3
End Sub

 

[jjt1973]

When I ran the macro on my sample worksheet it worked. However the real Macro isn't working. Can you tell me if you see an error: The actual macro is:

Sub channel()
'This macro will be used to hide row 3 if not active row.

If [E2].Value = "" Then
Range("A3:K3").EntireRow.Hidden = True
End If

If [E2].Value = Card Then
Range("A3:K3").EntireRow.Hidden = True
End If

If [E2].Value = NAS Then
Range("A3:K3").EntireRow.Hidden = True
End If

If [E2].Value = Retail Then
Range("A3:K3").EntireRow.Hidden = False
End If

If [E2].Value = "" Then
Range("A4:K4").EntireRow.Hidden = True
End If

If [E2].Value = Retail Then
Range("A4:K4").EntireRow.Hidden = True
End If

If [E2].Value = Card Then
Range("A4:K4").EntireRow.Hidden = True
End If

If [E2].Value = NAS Then
Range("A4:K4").EntireRow.Hidden = False
End If

End Sub

I have created a template that will be used by our national sales team. I have written a macro and will be adding more. This template will need to be able to refresh everytime it is opened so that the macros automatically turn on and refresh with each scenario. Here is the current macro running:

Sub channel()
'This macro will be used to hide row 3 if not active row.

If [E2].Value = "" Then
Range("A3:I3").EntireRow.Hidden = True
End If

If [E2].Value = MN Then
Range("A3:I3").EntireRow.Hidden = True
End If

If [E2].Value = NY Then
Range("A3:I3").EntireRow.Hidden = False
End If

If [E2].Value = WI Then
Range("A3:I3").EntireRow.Hidden = False
End If

End Sub

Thanks.

JJT

 

[VoG]

You are missing quotation signs, e.g.

If [E2].Value = "Card" Then

In the VBE, Tools > Options, tick Require Variable Declaration to trap this sort of error.

 

[jjt1973]

Thanks. The lines hid per the code but they don't unhide. Any ideas?

 

[VoG]

Instead of your If statements try like this

Range("A3:K3").EntireRow.Hidden = [E2].Value = ""

 

[jjt1973]

That code would hide the rows but what do I need to input for the rows to autounhide based on the code above?

 

[VoG]

That will unhide the rows if E2 isn't blank.