Exceute macro if no error

[mhdakhtar8]

Hi,

I've a macro which I run to validate if all the required sheets exist in a workbook or not. If no error, I run another macro to do some sets of action.

Requirement: How can I automatically run the second macro based on first? I want to avoid manually running the second macro.

Let me know if you need to see the codes.

 

[V_Malkoti]

Try this:

Sub Generate_Producitvity_Report()
    Dim OW As Workbook, ws As Worksheet, sname As Variant, i As Integer
    Dim sN As Variant, boolFlag As Boolean
    sname = Array("COS REJECT", "APPROVAL SENT", "Pending Client Response", _
        "Awaiting Four-Eye Check", "Awaiting RDS Approval", "SHEET6", "SHEET1")
    Set OW = ActiveWorkbook
    
    boolFlag = True
    For Each sN In sname
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(sN)
        If Err.Number > 0 Then Debug.Print sN & "sheet not found": Err.Clear: boolFlag = False
        On Error GoTo 0
    Next sN
    If boolFlag = False Then
         MyOtherSub
    End If
End Sub


Sub MyOtherSub()
    Debug.Print "Running MyOtherSub"
End Sub

 

[mhdakhtar8]

ok, cool. I test this out to see if it goes successful?

 

[V_Malkoti]

Sure. Just replace MyOtherSub and its call with your actual second macro.

 

[mhdakhtar8]

Yes, I already got that and I had to do it like this. You are superb!!!!

If boolFlag = True Then
         MyOtherSub
End If

 

[mhdakhtar8]

Since you have helped me so far, I hope you can help me out further. 1st macro perfectly works to check if any sheet is missing, it not it will run the next procedure.
Before I run the second procedure I'd also like check if each sheet has required columns headers (shown below).

Each sheet has some same column headers except "Sheet6" & "Sheet1". I'd also like to validate it before I run the next procedure.

Excel 2007
	A	B	C	D
1	REQUEST ID	APPROVAL PARTY	NO OF REQUEST	TIME TO COMPLETE
2
COS REJECT

 

[V_Malkoti]

Try this:

Sub Generate_Producitvity_Report()
    Dim OW As Workbook, ws As Worksheet, sname As Variant, i As Integer
    Dim sN As Variant, boolFlag As Boolean
    sname = Array("COS REJECT", "APPROVAL SENT", "Pending Client Response", _
        "Awaiting Four-Eye Check", "Awaiting RDS Approval", "SHEET6", "SHEET1")
    Set OW = ActiveWorkbook
    
    boolFlag = True
    For Each sN In sname
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(sN)
[COLOR=#0000cd]        If Err.Number > 0 Then [/COLOR]
[COLOR=#0000cd]		Debug.Print sN & "sheet not found"[/COLOR]
[COLOR=#0000cd]		Err.Clear[/COLOR]
[COLOR=#0000cd]		boolFlag = False[/COLOR]
[COLOR=#0000cd]	Else[/COLOR]
[COLOR=#0000cd]		' Check if this found sheet has required headers; SHEET1 and SHEET6 are exception from the rule[/COLOR]
[COLOR=#0000cd]		If ws.Name <> "SHEET1" and ws.Name <> "SHEET6" Then[/COLOR]
[COLOR=#0000cd]			If ws.Range("A1") <> "REQUEST ID" Or _[/COLOR]
[COLOR=#0000cd]				    ws.Range("B1") <> "APPROVAL PARTY" Or _[/COLOR]
[COLOR=#0000cd]				    ws.Range("C1") <> "NO OF REQUEST" Or _[/COLOR]
[COLOR=#0000cd]				    ws.Range("D1") <> "TIME TO COMPLETE" Then[/COLOR]
[COLOR=#0000cd]				boolFlag = False[/COLOR]
[COLOR=#0000cd]			End If[/COLOR]
[COLOR=#0000cd]		End If[/COLOR]
[COLOR=#0000cd]	End If[/COLOR]
        On Error GoTo 0
    Next sN
    If boolFlag = False Then
         MyOtherSub
    End If
End Sub




Sub MyOtherSub()
    Debug.Print "Running MyOtherSub"
End Sub

 

[mhdakhtar8]

Considerably appreciating. However I'm very much curious to understand how boolean value is playing role int this?

 

[V_Malkoti]

Boolean value acts as a flag to indicate if there was a violation of either rule : sheet not found or sheet found with incorrect header. If either of these rules is broken for any of the sheet, then the flag variable is set to False. At the end of procedure, if statement verifies the value of this variable, and calls second procedure only if the flag variable is never set to false (in the for loop above it).

 

[mhdakhtar8]

I ran that code but it's also validate the existence of ranges for "Sheet6" & "Sheet1". Below I've shared the Print result from Immediate window and the modified code.

COS Reject found
Approval Sent found
Pending Client Response found
Awaiting Four-Eye Check found
Awaiting RDS Approval found
Sheet6 found
Some Ranges are missing
Sheet1 found
Some Ranges are missing

Sub Generate_Producitvity_Report()
    Dim OW As Workbook, ws As Worksheet, sname As Variant, i As Integer
    Dim sN As Variant, boolFlag As Boolean
    sname = Array("COS REJECT", "APPROVAL SENT", "Pending Client Response", _
        "Awaiting Four-Eye Check", "Awaiting RDS Approval", "SHEET6", "SHEET1")
    Set OW = ActiveWorkbook
    
    boolFlag = True
    For Each sN In sname
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(sN)
        Debug.Print ws.Name; " found"
        If Err.Number > 0 Then
            Debug.Print sN & "sheet not found"
            Err.Clear
            boolFlag = False
        Else
            ' Check if this found sheet has required headers; SHEET1 and SHEET6 are exception from the rule
            If ws.Name <> "SHEET1" And ws.Name <> "SHEET6" Then
                 If ws.Range("A1") <> "requestid" Or _
                    ws.Range("B1") <> "Formname" Or _
                    ws.Range("C1") <> "Timestamp" Or _
                    ws.Range("D1") <> "Type" Or _
                    ws.Range("E1") <> "ActionBy" Then
                            Debug.Print "Some Ranges are missing"
                  End If
             End If
         End If
    
        On Error GoTo 0
    Next sN
    If boolFlag = False Then
         MyOtherSub
    End If
End Sub