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?