Macro to Call on multiple macros

[ffionnah]

Hi,
I want to either merge these macros or create a macro to call on them but i keep running into the "Run-time error'1004'. These macros add drop down lists to specific cells in each sheet. I have tried combining them but running them separately is the only way I have managed to get them to run through each sheet. What am I missing!?

Thank you!

MACRO 1

Sub DataValErrorsWG()
Dim Lastrow As Long

For Each Ws In Worksheets
Ws.Activate



Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("AG2:AG" & Lastrow).Validation.Delete
Application.ScreenUpdating = False

Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("AG2:AG" & Lastrow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="ERROR 1, ERROR 2, ERROR 3"
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True


Application.ActiveWindow.FreezePanes = True
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter

  'Range("A1").Select
   ' ActiveSheet.Range("$A:$Cj").AutoFilter Field:=1, Criteria1:=RGB(255, _
        '230, 153), Operator:=xlFilterCellColor
    
ActiveWindow.ScrollRow = 1
End If
Next Ws
End Sub

MACRO 2

Sub DataValYesNoWG()
Dim Lastrow As Long

For Each Ws In Worksheets
Ws.Activate


Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("AD2:AD" & Lastrow).Validation.Delete
Application.ScreenUpdating = False

Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("AD2:AD" & Lastrow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="NO,YES"
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True


Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("W2:W" & Lastrow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="NO,YES"
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True

Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("J2:M" & Lastrow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="NO,YES"
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True


Application.ActiveWindow.FreezePanes = True
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter

  'Range("A1").Select
   ' ActiveSheet.Range("$A:$Cj").AutoFilter Field:=1, Criteria1:=RGB(255, _
        '230, 153), Operator:=xlFilterCellColor
    
ActiveWindow.ScrollRow = 1
End If
Next Ws
End Sub

MACRO 3

Sub DataValPROGRAMWG()

Dim Lastrow As Long

For Each Ws In Worksheets
Ws.Activate



Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("V2:V" & Lastrow).Validation.Delete
Application.ScreenUpdating = False

Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("V2:V" & Lastrow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="PROGRAM 1,PROGRAM 2"
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True


Application.ActiveWindow.FreezePanes = True
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter

  'Range("A1").Select
   ' ActiveSheet.Range("$A:$Cj").AutoFilter Field:=1, Criteria1:=RGB(255, _
        '230, 153), Operator:=xlFilterCellColor
    
ActiveWindow.ScrollRow = 1
End If
Next Ws
End Sub

MACRO 4

Sub DataValSSSourceWG()
Dim Lastrow As Long

For Each Ws In Worksheets
Ws.Activate



Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("AJ2:AJ" & Lastrow).Validation.Delete
Application.ScreenUpdating = False

Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("AJ2:AJ" & Lastrow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=SS1,SS2,SS3"
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True


Application.ActiveWindow.FreezePanes = True
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter

  'Range("A1").Select
   ' ActiveSheet.Range("$A:$Cj").AutoFilter Field:=1, Criteria1:=RGB(255, _
        '230, 153), Operator:=xlFilterCellColor
    
ActiveWindow.ScrollRow = 1
End If
Next Ws
End Sub

 

[Roger Govier]

Create a new macro called RunAll

Sub RunAll()

Call DataValErrorsWG

Call DataValYesNoWG

Call DataValPROGRAMWG

Call DataValSSSourceWG

End Sub

Strictly speaking you don't need the Call on each line, but whenever I am running another macro form within a macro I liked to do this to show it is going outside of the current code.
You can add as many macros as you wish in such a fashion and each will be run in turn.

 

[ffionnah]

I'm not sure why but it worked this time. I have tried the call or aplication.run and it didnt work. Nevertheless, it works now