How can I exclude certain sheets from VBA code?

Viewed 865

I want to try and exclude certain sheets in my workbook from the actions the VBA code is designed for. it basically compares all sheets against each other and winds up giving me any duplicates found in a new sheet called Confirmed Lays. I'm not sure if it is the most efficient way to do it, but it does work.

Option Explicit

Public critLR As Long
Public sbLayLR As Long
Public faLays1LR As Long
Public faLays2LR As Long
Public confLaysLR As Long
Public ws As Worksheet
Public wb As Workbook
Public currentWS As Worksheet
Public currentWSLastRow As Long
Public CritWSLastRow As Long
Dim CritWS As Worksheet

Sub LayRunOrder()

Call SetUp
Call LoopWSs
Call FinishUP

End Sub

Sub SetUp()

For Each ws _
In ActiveWorkbook.Sheets

Select Case ws.Name
    Case Is = "Safe Bets", "PP1", "PP2", "FA Racing", "FA Racing 2", "FA Racing 3", "Debut Destroyer"
        'Do Nothing
    Case Else
   ws.Tab.Color = xlNone

'ws.Range("a1").CurrentRegion.Columns.AutoFit
'ws.Range("a1").CurrentRegion.Rows.AutoFit

    If ws.FilterMode = True Then
        ws.ShowAllData
    End If

    If ws.AutoFilterMode = True Then
        ws.AutoFilterMode = False
    End If

    If ws.Name = "Criteria" Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
End Select

Next ws

Worksheets.Add.Name = "Criteria"
Worksheets("Confirmed Lays").Range("1:1").Copy Worksheets("Criteria").Range("1:1")

End Sub

Sub LoopWSs()

For Each CritWS In ThisWorkbook.Worksheets
Select Case ws.Name
    Case Is = "Safe Bets", "PP1", "PP2", "FA Racing", "FA Racing 2", "FA Racing 3", "Debut Destroyer"
        'Do Nothing
    Case Else

    CritWSLastRow = CritWS.Cells(Rows.Count, 1).End(xlUp).Row

    For Each currentWS In ThisWorkbook.Worksheets
        If CritWS.Name = currentWS.Name Then
            GoTo Skip
        End If

        If currentWS.Name = "Criteria" Then
            GoTo Skip
        End If
        If currentWS.Name = "Confirmed Lays" Then
            GoTo Skip
        End If

        currentWSLastRow = currentWS.Cells(Rows.Count, 1).End(xlUp).Row
        Call FilterWSs
        currentWS.Tab.Color = vbWhite
Skip:
    Next currentWS
    CritWS.Tab.Color = vbWhite
Next CritWS
End Select

End Sub

Sub FilterWSs()

    CritWS.Range("a2:a" & CritWSLastRow).Copy Worksheets("Criteria").Range("a2")
    CritWS.Range("b2:b" & CritWSLastRow).Copy Worksheets("Criteria").Range("b2")
    CritWS.Range("h2:h" & CritWSLastRow).Copy Worksheets("Criteria").Range("h2")

    currentWS.Activate

    If currentWS.Cells(Rows.Count, 1).End(xlUp).Row < 2 Then
        GoTo Skipfilter
   End If

    confLaysLR = Worksheets("Confirmed Lays").Cells(Rows.Count, 1).End(xlUp).Row

'Range("A1:W" & currentWSLastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Criteria").Range("A1:W" & critLR), Unique:=False
Range("A1:W" & currentWSLastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A1:W" & CritWSLastRow), _
    copytorange:=Sheets("Confirmed Lays").Range("A" & confLaysLR + 1), Unique:=False

'Range("a2").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Worksheets("Confirmed Lays").Range("a" & confLaysLR + 1)

Skipfilter:

End Sub

Sub FinishUP()

Application.DisplayAlerts = False
Worksheets("Criteria").Delete
Application.DisplayAlerts = True

Worksheets("Confirmed Lays").Activate
    Range("a:x").RemoveDuplicates Columns:=Array(1, 2, 8), Header:=xlYes

End Sub

Sub Timer()

Dim sT As Double
Dim eT As Double
Dim TimeTaken As Variant

sT = Now()

Call LayRunOrder

TimeTaken = Format((Now() - sT), "HH:mm:ss")
Debug.Print TimeTaken

End Sub

I have looked at possibly using Select Case to exclude the sheets in question, but couldn't get it to work at all.

This is what I put together to hopefully do the exclusion of the sheets. I tried inputting it in the SetUp macro, but was really floundering as to what to have in Case Else. I tried having all the rest of the code for that particular macro in there next and ending with End Select, but it failed to work.

Sub SetUp()

    Dim ws As Worksheet
    Dim wb As Workbook
    Select Case ws.CodeName
    Case "Safe Bets", "PP1", "PP2", "FA Racing", "FA Racing 2", FA Racing 3”, "Debut Destroyer"
    Case Else
        ws.Tab.Color = xlNone

'ws.Range("a1").CurrentRegion.Columns.AutoFit
'ws.Range("a1").CurrentRegion.Rows.AutoFit

    If ws.FilterMode = True Then
        ws.ShowAllData
    End If

    If ws.AutoFilterMode = True Then
        ws.AutoFilterMode = False
    End If

    If ws.Name = "Criteria" Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
    End Select
Next ws

Worksheets.Add.Name = "Criteria"
Worksheets("Confirmed Lays").Range("1:1").Copy Worksheets("Criteria").Range("1:1")

End Sub

Any suggestions as to how to exclude the worksheets listed from my larger VBA code?

1 Answers

on the base of your new code i did some amendments to make it better readable and somit corrections. Also I put some things in to make it faster.
What I still do not unterstand is what you do in "LoopWSs" - you are doing a double loop there, meaning if you have 10 worksheets, you have 10x10=100 times the loop running. But if it works, why bother...

    Option Explicit

    Public critLR As Long
    Public sbLayLR As Long
    Public faLays1LR As Long
    Public faLays2LR As Long
    Public confLaysLR As Long
    Public ws As Worksheet
    Public wb As Workbook
    Public currentWS As Worksheet
    Public currentWSLastRow As Long
    Public CritWSLastRow As Long
    Dim CritWS As Worksheet

    Sub Timer()
    Dim sT As Double
    Dim eT As Double
    Dim TimeTaken As Variant

    sT = Now()

    Call LayRunOrder

    TimeTaken = format((Now() - sT), "HH:mm:ss")
    Debug.Print TimeTaken
    End Sub

    Sub LayRunOrder()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual ' dann aber wo notwendig Application.Calculate

    Call SetUp
    Call LoopWSs
    Call FinishUP

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub

    Sub SetUp()

    Dim sheetsArray As Sheets
    Set sheetsArray = ActiveWorkbook.Sheets(Array("Safe Bets Lay", "FA Lays 1", "FA Lays 2"))

    Dim sheetObject As Worksheet

    ' change value of range 'a1' on each sheet from sheetsArray
    For Each sheetObject In sheetsArray
        'Do something
        ws.Tab.Color = xlNone
        If ws.FilterMode = True Then ws.ShowAllData
        If ws.AutoFilterMode = True Then ws.AutoFilterMode = False

Next sheetObject

Worksheets.Add.Name = "Criteria"
Worksheets("Confirmed Lays").Range("1:1").Copy Worksheets("Criteria").Range("1:1")

End Sub

    Sub LoopWSs()

    For Each CritWS In ThisWorkbook.Worksheets
    Select Case CritWS.Name
        Case Is = "Safe Bets", "PP1", "PP2", "FA Racing", "FA Racing 2", "FA Racing 3", "Debut Destroyer"
            'Do Nothing
        Case Else

        CritWSLastRow = CritWS.Cells(Rows.Count, 1).End(xlUp).Row

        For Each currentWS In ThisWorkbook.Worksheets
            If CritWS.Name = currentWS.Name Then GoTo Skip
            If currentWS.Name = "Criteria" Then GoTo Skip
            If currentWS.Name = "Confirmed Lays" Then GoTo Skip

            currentWSLastRow = currentWS.Cells(Rows.Count, 1).End(xlUp).Row
            Call FilterWSs
            currentWS.Tab.Color = vbWhite

    Skip:
        Next currentWS
        CritWS.Tab.Color = vbWhite
    End Select
    Next CritWS

    End Sub

    Sub FilterWSs()

        CritWS.Range("a2:a" & CritWSLastRow).Copy Worksheets("Criteria").Range("a2")
        CritWS.Range("b2:b" & CritWSLastRow).Copy Worksheets("Criteria").Range("b2")
        CritWS.Range("h2:h" & CritWSLastRow).Copy Worksheets("Criteria").Range("h2")

        currentWS.Activate

        If currentWS.Cells(Rows.Count, 1).End(xlUp).Row < 2 Then
            GoTo Skipfilter
       End If

        confLaysLR = Worksheets("Confirmed Lays").Cells(Rows.Count, 1).End(xlUp).Row

    'Range("A1:W" & currentWSLastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Criteria").Range("A1:W" & critLR), Unique:=False
    Range("A1:W" & currentWSLastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A1:W" & CritWSLastRow), _
        copytorange:=Sheets("Confirmed Lays").Range("A" & confLaysLR + 1), Unique:=False

    'Range("a2").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Worksheets("Confirmed Lays").Range("a" & confLaysLR + 1)

    Skipfilter:

    End Sub

    Sub FinishUP()

    Application.DisplayAlerts = False
    Worksheets("Criteria").Delete
    Application.DisplayAlerts = True

    Worksheets("Confirmed Lays").Activate
        Range("a:x").RemoveDuplicates Columns:=Array(1, 2, 8), Header:=xlYes

    End Sub
Related