Call to second macro doesn't work if no results in original

[honkin]

I have a sheet which I run a VBA macro on and that macro has a call to run an additional macro. What I am finding is that if there are no selections when the first macro is run, it does not continue to run the second; only if there ARE selections in the first.

I have obviously missed something in the coding and I wonder if someone can spot the error. By the way, the second macro runs fine on its own

Sub SB_20_Lay()
'
' Distance, CD Winner, Last Start, Handicaps, Forecast Rank
'
    Dim ws As Worksheet, lc As Long, lr As Long

    Set ws = ActiveSheet
    'range from A1 to last column header and last row
    lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Application
             .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .DisplayStatusBar = False
            .EnableEvents = False
    End With
    With ws.Range("A1", ws.Cells(lr, lc))
            .HorizontalAlignment = xlCenter
            .AutoFilter Field:=10, Criteria1:="=4"
            .AutoFilter Field:=11, Criteria1:=">=2000"
            .AutoFilter Field:=62, Criteria1:="=2", Operator:=xlOr, Criteria2:="=3"
            .AutoFilter Field:=3, Criteria1:="*Handicap*"
            .AutoFilter Field:=66, Criteria1:=Array("2", "5", "6"), Operator:=xlFilterValues
            .AutoFilter Field:=73, Criteria1:=Array("4", "5", "6", "8", "9"), Operator:=xlFilterValues
         If .Rows.Count - 1 > 0 Then
        On Error Resume Next
            .Columns("C:C").EntireColumn.Hidden = True
            .Columns("G:G").EntireColumn.Hidden = True
            .Columns("I:I").EntireColumn.Hidden = True
            .Columns("K:L").EntireColumn.Hidden = True
            .Columns("N:W").EntireColumn.Hidden = True
            .Columns("Z:Z").EntireColumn.Hidden = True
            .Columns("AB:AK").EntireColumn.Hidden = True
            .Columns("AO:AO").EntireColumn.Hidden = True
            .Columns("AQ:BI").EntireColumn.Hidden = True
            .Columns("BK:BL").EntireColumn.Hidden = True
            .Columns("BO:BS").EntireColumn.Hidden = True
            .Columns("BV:BY").EntireColumn.Hidden = True
            .Columns("CA:CA").EntireColumn.Hidden = True
            .Columns("CC:CG").EntireColumn.Hidden = True
            .Columns("CI:CK").EntireColumn.Hidden = True
        If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
        Else
            Exit Sub
        End If
            On Error GoTo 0
        End If
    End With
     
    Workbooks("New Results File Active Football Advisor.xlsm").Sheets("Safe Bets Lay") _
          .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   
    Application.CutCopyMode = False
    With Application
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
            .DisplayStatusBar = True
            .EnableEvents = True
    End With
    Call Reset_Sheet
    Call SB_20_Lay_2
End Sub

As you can see the call firstly is to a macro which simply resets the sheet back to an unfiltered state, but it is the next call which only happens if there are selections from the original macro

cheers

 

[Jamie McMillan]

Hello Honkin,

You have exit sub in your code - It will stop there -

If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
Else
Exit Sub
End If

You can just delete

Else
Exit Sub

The 'if' command doesn't need an 'Else' if you don't want it to do anything else.

Jamie

 

[honkin]

Hello Honkin,

You have exit sub in your code - It will stop there -

If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
Else
Exit Sub
End If

You can just delete

Else
Exit Sub

The 'if' command doesn't need an 'Else' if you don't want it to do anything else.

Jamie

Cheers Jamie. That's great