Ignore worksheet on lookup

S

StillUnderstanding

Board Regular
Joined
Jan 30, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I wonder if someone might be able to help me with an issue around ignoring tabs in a workbook. I looking to have the macro run but to totally ignore 2 tabs, below is the code that I have used but it is not ignoring the tabs listed.


"For Each wSht In ThisWorkbook.Worksheets
If wSht.Name = "Lookup" Then
ElseIf wSht.Name = "Sheet14" Then
Else
End If"

I am clearly doing something wrong but just cant work it out.

Here is the full macro code, it would be great if someone could help!

Thanks

VBA Code: 
Option Explicit
Option Compare Text



Sub Lookup_Report()
Application.ScreenUpdating = False
Dim wSht As Worksheet, crow As Long, frow As Long, i As Long, wThis As Worksheet
Dim searchCode As String


Set wThis = Sheet15
searchCode = Trim(wThis.Range("B3"))
If Len(searchCode) = 0 Then
MsgBox "Please enter your Unique ID into cell B3 to search", vbOKOnly
ActiveSheet.Range("B3").Select
Exit Sub
End If


wThis.Range("A7:An" & Rows.Count) = Empty
wThis.Range("A7:An" & Rows.Count).ClearFormats
 

crow = 7

     For Each wSht In ThisWorkbook.Worksheets
     ' If wSht.Name = "Lookup" Or wSht.Name = "Sheet14" Then
      ' do nothing
       If wSht.Name = "Lookup" Then
    ElseIf wSht.Name = "Sheet14" Then
    

    Else
     ' If wSht.Name <> "Lookup" And wSht.Name <> "Sheet14" Then
      '  Debug.Print wSht.Name
     'Else
    
    
     End If
    Dim Rng As Range
        
    Dim CnumA As Integer
    Dim CnumB As Integer
    Dim CnumC As Integer
    Dim CnumD As Integer
    Dim CnumE As Integer
    Dim CnumF As Integer
    Dim CnumG As Integer
    Dim CnumH As Integer
    Dim CnumI As Integer
    Dim CnumJ As Integer
    Dim CnumK As Integer
    Dim CnumL As Integer
    Dim CnumM As Integer
    Dim CnumN As Integer
    Dim CnumO As Integer
    Dim CnumP As Integer
    Dim CnumQ As Integer
    Dim CnumR As Integer
    Dim CnumS As Integer
    Dim CnumT As Integer
    Dim CnumU As Integer
    Dim CnumV As Integer
    Dim Cnumw As Integer
    Dim Cnumx As Integer
    Dim Cnumy As Integer
    Dim Cnumz As Integer
    Dim CnumAA As Integer
    Dim CnumAB As Integer
    Dim CnumAC As Integer
    Dim CnumAD As Integer
    Dim Cnumae As Integer
    Dim CnumAf As Integer
    Dim CnumAg As Integer
    Dim CnumAh As Integer
    Dim CnumAI As Integer
    Dim CnumAJ As Integer
    Dim CnumAk As Integer
    Dim CnumAl As Integer
    Dim CnumAm As Integer
    Dim CnumAn As Integer
    
    Dim LookCnum As Integer
    
    Dim ColA As String
    Dim ColB As String
    Dim ColC As String
    Dim ColD As String
    Dim ColE As String
    Dim ColF As String
    Dim ColG As String
    Dim ColH As String
    Dim ColI As String
    Dim ColJ As String
    Dim ColK As String
    Dim ColL As String
    Dim ColM As String
    Dim ColN As String
    Dim ColO As String
    Dim ColP As String
    Dim ColQ As String
    Dim ColR As String
    Dim ColS As String
    Dim ColT As String
    Dim ColU As String
    Dim ColV As String
    Dim Colw As String
    Dim Colx As String
    Dim Coly As String
    Dim Colz As String
    Dim ColAA As String
    Dim ColAB As String
    Dim ColAC As String
    Dim ColAD As String
    Dim Colae As String
    Dim ColAf As String
    Dim ColAg As String
    Dim ColAh As String
    Dim ColAI As String
    Dim ColAJ As String
    Dim ColAk As String
    Dim ColAl As String
    Dim ColAm As String
    Dim ColAn As String
    
    
    
    Dim LookCol As String
    
    ColA = Cells(6, 1).Value
    ColB = Cells(6, 2).Value
    ColC = Cells(6, 3).Value
    ColD = Cells(6, 4).Value
    ColE = Cells(6, 5).Value
    ColF = Cells(6, 6).Value
    ColG = Cells(6, 7).Value
    ColH = Cells(6, 8).Value
    ColI = Cells(6, 9).Value
    ColJ = Cells(6, 10).Value
    ColK = Cells(6, 11).Value
    ColL = Cells(6, 12).Value
    ColM = Cells(6, 13).Value
    ColN = Cells(6, 14).Value
    ColO = Cells(6, 15).Value
    ColP = Cells(6, 16).Value
    ColQ = Cells(6, 17).Value
    ColR = Cells(6, 18).Value
    ColS = Cells(6, 19).Value
    ColT = Cells(6, 20).Value
    ColU = Cells(6, 21).Value
    ColV = Cells(6, 22).Value
    Colw = Cells(6, 23).Value
    Colx = Cells(6, 24).Value
    Coly = Cells(6, 25).Value
    Colz = Cells(6, 26).Value
    ColAA = Cells(6, 27).Value
    ColAB = Cells(6, 28).Value
    ColAC = Cells(6, 29).Value
    ColAD = Cells(6, 30).Value
    Colae = Cells(6, 31).Value
    ColAf = Cells(6, 32).Value
    ColAg = Cells(6, 33).Value
    ColAh = Cells(6, 34).Value
    ColAI = Cells(6, 35).Value
    ColAJ = Cells(6, 36).Value
    ColAk = Cells(6, 37).Value
    ColAl = Cells(6, 38).Value
    ColAm = Cells(6, 39).Value
    ColAn = Cells(6, 40).Value
    Set Rng = Range("a6:an6")
    Application.CutCopyMode = False 'You only need the headers and not all the table
    
    'variable used to filter data
    LookCol = Cells(2, 2).Value
    
    LookCnum = Application.WorksheetFunction.Match(LookCol, Rng, 0)
    
    LookCol = Split(Cells(1, LookCnum).Address, "$")(1)
    
    'looking for column numbers
    CnumA = Application.WorksheetFunction.Match(ColA, Rng, 0)
    CnumB = Application.WorksheetFunction.Match(ColB, Rng, 0)
    CnumC = Application.WorksheetFunction.Match(ColC, Rng, 0)
    CnumD = Application.WorksheetFunction.Match(ColD, Rng, 0)
    CnumE = Application.WorksheetFunction.Match(ColE, Rng, 0)
    CnumF = Application.WorksheetFunction.Match(ColF, Rng, 0)
    CnumG = Application.WorksheetFunction.Match(ColG, Rng, 0)
    CnumH = Application.WorksheetFunction.Match(ColH, Rng, 0)
    CnumI = Application.WorksheetFunction.Match(ColI, Rng, 0)
    CnumJ = Application.WorksheetFunction.Match(ColJ, Rng, 0)
    CnumK = Application.WorksheetFunction.Match(ColK, Rng, 0)
    CnumL = Application.WorksheetFunction.Match(ColL, Rng, 0)
    CnumM = Application.WorksheetFunction.Match(ColM, Rng, 0)
    CnumN = Application.WorksheetFunction.Match(ColN, Rng, 0)
    CnumO = Application.WorksheetFunction.Match(ColO, Rng, 0)
    CnumP = Application.WorksheetFunction.Match(ColP, Rng, 0)
    CnumQ = Application.WorksheetFunction.Match(ColQ, Rng, 0)
    CnumR = Application.WorksheetFunction.Match(ColR, Rng, 0)
    CnumS = Application.WorksheetFunction.Match(ColS, Rng, 0)
    CnumT = Application.WorksheetFunction.Match(ColT, Rng, 0)
    CnumU = Application.WorksheetFunction.Match(ColU, Rng, 0)
    CnumV = Application.WorksheetFunction.Match(ColV, Rng, 0)
    Cnumw = Application.WorksheetFunction.Match(Colw, Rng, 0)
    Cnumx = Application.WorksheetFunction.Match(Colx, Rng, 0)
    Cnumy = Application.WorksheetFunction.Match(Coly, Rng, 0)
    Cnumz = Application.WorksheetFunction.Match(Colz, Rng, 0)
    CnumAA = Application.WorksheetFunction.Match(ColAA, Rng, 0)
    CnumAB = Application.WorksheetFunction.Match(ColAB, Rng, 0)
    CnumAC = Application.WorksheetFunction.Match(ColAC, Rng, 0)
    CnumAD = Application.WorksheetFunction.Match(ColAD, Rng, 0)
    Cnumae = Application.WorksheetFunction.Match(Colae, Rng, 0)
    CnumAf = Application.WorksheetFunction.Match(ColAf, Rng, 0)
    CnumAg = Application.WorksheetFunction.Match(ColAg, Rng, 0)
    CnumAh = Application.WorksheetFunction.Match(ColAh, Rng, 0)
    CnumAI = Application.WorksheetFunction.Match(ColAI, Rng, 0)
    CnumAJ = Application.WorksheetFunction.Match(ColAJ, Rng, 0)
    CnumAk = Application.WorksheetFunction.Match(ColAk, Rng, 0)
    CnumAl = Application.WorksheetFunction.Match(ColAl, Rng, 0)
    CnumAm = Application.WorksheetFunction.Match(ColAm, Rng, 0)
    CnumAn = Application.WorksheetFunction.Match(ColAn, Rng, 0)
    
        
    'Convert To Column Letter
    ColA = Split(Cells(1, CnumA).Address, "$")(1)
    ColB = Split(Cells(1, CnumB).Address, "$")(1)
    ColC = Split(Cells(1, CnumC).Address, "$")(1)
    ColD = Split(Cells(1, CnumD).Address, "$")(1)
    ColE = Split(Cells(1, CnumE).Address, "$")(1)
    ColF = Split(Cells(1, CnumF).Address, "$")(1)
    ColG = Split(Cells(1, CnumG).Address, "$")(1)
    ColH = Split(Cells(1, CnumH).Address, "$")(1)
    ColI = Split(Cells(1, CnumI).Address, "$")(1)
    ColJ = Split(Cells(1, CnumJ).Address, "$")(1)
    ColK = Split(Cells(1, CnumK).Address, "$")(1)
    ColL = Split(Cells(1, CnumL).Address, "$")(1)
    ColM = Split(Cells(1, CnumM).Address, "$")(1)
    ColN = Split(Cells(1, CnumN).Address, "$")(1)
    ColO = Split(Cells(1, CnumO).Address, "$")(1)
    ColP = Split(Cells(1, CnumP).Address, "$")(1)
    ColQ = Split(Cells(1, CnumQ).Address, "$")(1)
    ColR = Split(Cells(1, CnumR).Address, "$")(1)
    ColS = Split(Cells(1, CnumS).Address, "$")(1)
    ColT = Split(Cells(1, CnumT).Address, "$")(1)
    ColU = Split(Cells(1, CnumU).Address, "$")(1)
    ColV = Split(Cells(1, CnumV).Address, "$")(1)
    Colw = Split(Cells(1, Cnumw).Address, "$")(1)
    Colx = Split(Cells(1, Cnumx).Address, "$")(1)
    Coly = Split(Cells(1, Cnumy).Address, "$")(1)
    Colz = Split(Cells(1, Cnumz).Address, "$")(1)
    ColAA = Split(Cells(1, CnumAA).Address, "$")(1)
    ColAB = Split(Cells(1, CnumAB).Address, "$")(1)
    ColAC = Split(Cells(1, CnumAC).Address, "$")(1)
    ColAD = Split(Cells(1, CnumAD).Address, "$")(1)
    Colae = Split(Cells(1, Cnumae).Address, "$")(1)
    ColAf = Split(Cells(1, CnumAf).Address, "$")(1)
    ColAg = Split(Cells(1, CnumAg).Address, "$")(1)
    ColAh = Split(Cells(1, CnumAh).Address, "$")(1)
    ColAI = Split(Cells(1, CnumAI).Address, "$")(1)
    ColAJ = Split(Cells(1, CnumAJ).Address, "$")(1)
    ColAk = Split(Cells(1, CnumAk).Address, "$")(1)
    ColAl = Split(Cells(1, CnumAl).Address, "$")(1)
    ColAm = Split(Cells(1, CnumAm).Address, "$")(1)
    ColAn = Split(Cells(1, CnumAn).Address, "$")(1)
 
    frow = wSht.Range("Af" & Rows.Count).End(xlUp).Row
    
    For i = 2 To frow
        If wSht.Range(LookCol & i) = searchCode Then
            wThis.Range("A" & crow) = wSht.Range(ColA & i)
            wThis.Range("A" & crow).Cells.Interior.Color = wSht.Range(ColA & i).Cells.Interior.Color
            wThis.Range("A" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                
            wThis.Range("B" & crow) = wSht.Range(ColB & i)
            wThis.Range("B" & crow).Cells.Interior.Color = wSht.Range(ColB & i).Cells.Interior.Color
            wThis.Range("B" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                
            wThis.Range("C" & crow) = wSht.Range(ColC & i)
            wThis.Range("C" & crow).Cells.Interior.Color = wSht.Range(ColC & i).Cells.Interior.Color
            wThis.Range("C" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                
            wThis.Range("d" & crow) = wSht.Range(ColD & i)
            wThis.Range("D" & crow).Cells.Interior.Color = wSht.Range(ColD & i).Cells.Interior.Color
            wThis.Range("D" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                
            wThis.Range("e" & crow) = wSht.Range(ColE & i)
            wThis.Range("E" & crow).Cells.Interior.Color = wSht.Range(ColE & i).Cells.Interior.Color
            wThis.Range("E" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("f" & crow) = wSht.Range(ColF & i)
            wThis.Range("F" & crow).Cells.Interior.Color = wSht.Range(ColF & i).Cells.Interior.Color
            wThis.Range("F" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("g" & crow) = wSht.Range(ColG & i)
            wThis.Range("G" & crow).Cells.Interior.Color = wSht.Range(ColG & i).Cells.Interior.Color
            wThis.Range("G" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("h" & crow) = wSht.Range(ColH & i)
            wThis.Range("H" & crow).Cells.Interior.Color = wSht.Range(ColH & i).Cells.Interior.Color
            wThis.Range("H" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("i" & crow) = wSht.Range(ColI & i)
            wThis.Range("I" & crow).Cells.Interior.Color = wSht.Range(ColI & i).Cells.Interior.Color
            wThis.Range("I" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("j" & crow) = wSht.Range(ColJ & i)
            wThis.Range("J" & crow).Cells.Interior.Color = wSht.Range(ColJ & i).Cells.Interior.Color
            wThis.Range("J" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("k" & crow) = wSht.Range(ColK & i)
            wThis.Range("K" & crow).Cells.Interior.Color = wSht.Range(ColK & i).Cells.Interior.Color
            wThis.Range("K" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("l" & crow) = wSht.Range(ColL & i)
            wThis.Range("L" & crow).Cells.Interior.Color = wSht.Range(ColL & i).Cells.Interior.Color
            wThis.Range("L" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("m" & crow) = wSht.Range(ColM & i)
            wThis.Range("M" & crow).Cells.Interior.Color = wSht.Range(ColM & i).Cells.Interior.Color

             wThis.Range("M" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("n" & crow) = wSht.Range(ColN & i)
            wThis.Range("N" & crow).Cells.Interior.Color = wSht.Range(ColN & i).Cells.Interior.Color
            wThis.Range("N" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("o" & crow) = wSht.Range(ColO & i)
            wThis.Range("O" & crow).Cells.Interior.Color = wSht.Range(ColO & i).Cells.Interior.Color
            wThis.Range("O" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("p" & crow) = wSht.Range(ColP & i)
            wThis.Range("P" & crow).Cells.Interior.Color = wSht.Range(ColP & i).Cells.Interior.Color
            wThis.Range("P" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("q" & crow) = wSht.Range(ColQ & i)
            wThis.Range("Q" & crow).Cells.Interior.Color = wSht.Range(ColQ & i).Cells.Interior.Color
            wThis.Range("Q" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("r" & crow) = wSht.Range(ColR & i)
            wThis.Range("R" & crow).Cells.Interior.Color = wSht.Range(ColR & i).Cells.Interior.Color
            wThis.Range("R" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("S" & crow) = wSht.Range(ColS & i)
            wThis.Range("S" & crow).Cells.Interior.Color = wSht.Range(ColS & i).Cells.Interior.Color
            wThis.Range("S" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("T" & crow) = wSht.Range(ColT & i)
            wThis.Range("T" & crow).Cells.Interior.Color = wSht.Range(ColT & i).Cells.Interior.Color
            wThis.Range("T" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("U" & crow) = wSht.Range(ColU & i)
            wThis.Range("U" & crow).Cells.Interior.Color = wSht.Range(ColU & i).Cells.Interior.Color
            wThis.Range("U" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("V" & crow) = wSht.Range(ColV & i)
            wThis.Range("V" & crow).Cells.Interior.Color = wSht.Range(ColV & i).Cells.Interior.Color
            wThis.Range("V" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("W" & crow) = wSht.Range(Colw & i)
            wThis.Range("W" & crow).Cells.Interior.Color = wSht.Range(Colw & i).Cells.Interior.Color
            wThis.Range("W" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("X" & crow) = wSht.Range(Colx & i)
            wThis.Range("X" & crow).Cells.Interior.Color = wSht.Range(Colx & i).Cells.Interior.Color
            wThis.Range("X" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("Y" & crow) = wSht.Range(Coly & i)
            wThis.Range("Y" & crow).Cells.Interior.Color = wSht.Range(Coly & i).Cells.Interior.Color
            wThis.Range("Y" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("Z" & crow) = wSht.Range(Colz & i)
            wThis.Range("Z" & crow).Cells.Interior.Color = wSht.Range(Colz & i).Cells.Interior.Color
            wThis.Range("Z" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AA" & crow) = wSht.Range(ColAA & i)
            wThis.Range("AA" & crow).Cells.Interior.Color = wSht.Range(ColAA & i).Cells.Interior.Color
            wThis.Range("AA" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AB" & crow) = wSht.Range(ColAB & i)
            wThis.Range("AB" & crow).Cells.Interior.Color = wSht.Range(ColAB & i).Cells.Interior.Color
            wThis.Range("AB" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AC" & crow) = wSht.Range(ColAC & i)
            wThis.Range("AC" & crow).Cells.Interior.Color = wSht.Range(ColAC & i).Cells.Interior.Color
            wThis.Range("AC" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AD" & crow) = wSht.Range(ColAD & i)
            wThis.Range("AD" & crow).Cells.Interior.Color = wSht.Range(ColAD & i).Cells.Interior.Color
            wThis.Range("AD" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AE" & crow) = wSht.Range(Colae & i)
            wThis.Range("AE" & crow).Cells.Interior.Color = wSht.Range(Colae & i).Cells.Interior.Color
            wThis.Range("AE" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AF" & crow) = wSht.Range(ColAf & i)
            wThis.Range("AF" & crow).Cells.Interior.Color = wSht.Range(ColAf & i).Cells.Interior.Color
            wThis.Range("AF" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AG" & crow) = wSht.Range(ColAg & i)
            wThis.Range("AG" & crow).Cells.Interior.Color = wSht.Range(ColAg & i).Cells.Interior.Color
            wThis.Range("AG" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AH" & crow) = wSht.Range(ColAh & i)
            wThis.Range("AH" & crow).Cells.Interior.Color = wSht.Range(ColAh & i).Cells.Interior.Color
            wThis.Range("AH" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                 wThis.Range("AI" & crow) = wSht.Range(ColAI & i)
            wThis.Range("AI" & crow).Cells.Interior.Color = wSht.Range(ColAI & i).Cells.Interior.Color
            wThis.Range("AI" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                 wThis.Range("AJ" & crow) = wSht.Range(ColAJ & i)
            wThis.Range("AJ" & crow).Cells.Interior.Color = wSht.Range(ColAJ & i).Cells.Interior.Color
            wThis.Range("AJ" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                 wThis.Range("Ak" & crow) = wSht.Range(ColAk & i)
            wThis.Range("Ak" & crow).Cells.Interior.Color = wSht.Range(ColAk & i).Cells.Interior.Color
            wThis.Range("Ak" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                       wThis.Range("Al" & crow) = wSht.Range(ColAl & i)
            wThis.Range("Al" & crow).Cells.Interior.Color = wSht.Range(ColAl & i).Cells.Interior.Color
            wThis.Range("Al" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                 wThis.Range("Am" & crow) = wSht.Range(ColAm & i)
            wThis.Range("Am" & crow).Cells.Interior.Color = wSht.Range(ColAm & i).Cells.Interior.Color
            wThis.Range("Am" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                 wThis.Range("An" & crow) = wSht.Range(ColAn & i)
            wThis.Range("An" & crow).Cells.Interior.Color = wSht.Range(ColAn & i).Cells.Interior.Color
            wThis.Range("An" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            crow = crow + 1
        End If
    Next i



    
 
'End If
Application.ScreenUpdating = True
Range("A6:an50").WrapText = True

Range("a6:an50").HorizontalAlignment = xlCenter

Range("j:j,y:y").Select
    Selection.NumberFormat = "dd/mm/yy;@"
Range("b3").Select
     Application.Calculation = xlAutomatic
 
    
    
Next wSht
    


         '---------------------------------------------------
      Dim lastrow As Long

lastrow = Cells(Rows.Count, "c").End(xlUp).Row
    
    ActiveSheet.Range("c6:c" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=ActiveSheet.Range("y1:y4"), _
    Unique:=True
     Range("U7:An7").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
      
    End With
    Selection.Font.Bold = True
    
    '---------------------------------------------
     'Range("U6:An6").Select
    'Selection.Delete Shift:=xlUp
    Range("U7:An9").Select
    Selection.Delete Shift:=xlUp
      
    Range("U7:U41").Select
    Selection.ColumnWidth = 5.73
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
        
        Range("$u$6").Value = "Blank"
    Range("$u$6").Select
    With Selection.Font
        .Color = -11480942
        .TintAndShade = 0
    Range("U7").Select
    
     End With
    Selection.Font.Bold = True
    
    
 
        
       ' ------------------------add tally to top of sheet------------------------
Range("z2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$T$100,10,FALSE), """")"
Range("z3").Formula = "=IFERROR(VLOOKUP($Y3,$C$7:$T$100,10,FALSE), """")"
Range("z4").Formula = "=IFERROR(VLOOKUP($Y4,$C$7:$T$100,10,FALSE), """")"
Range("aa2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$AC$100,24,FALSE), """")"
Range("aa3").Formula = "=IFERROR(VLOOKUP($Y3,$C$7:$AC$100,24,FALSE), """")"
Range("aa4").Formula = "=IFERROR(VLOOKUP($Y4,$C$7:$AC$100,24,FALSE), """")"
Range("ab2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$T$100,11,FALSE), """")"
Range("ab3").Formula = "=IFERROR(VLOOKUP($Y3,$C$7:$T$100,11,FALSE), """")"
Range("ab4").Formula = "=IFERROR(VLOOKUP($Y4,$C$7:$T$100,11,FALSE), """")"
Range("ac2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$AC$100,25,FALSE), """")"
Range("ac3").Formula = "=IFERROR(VLOOKUP($Y3,$C$7:$AC$100,25,FALSE), """")"
Range("ac4").Formula = "=IFERROR(VLOOKUP($Y4,$C$7:$AC$100,25,FALSE), """")"
Range("ad2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$T$100,12,FALSE), """")"
Range("ad3").Formula = "=IFERROR(VLOOKUP($Y3,$C$7:$T$100,12,FALSE), """")"
Range("ad4").Formula = "=IFERROR(VLOOKUP($Y4,$C$7:$T$100,12,FALSE), """")"
Range("ae2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$AC$100,26,FALSE), """")"
Range("ae3").Formula = "=IFERROR(VLOOKUP($Y3,$C$7:$AC$100,26,FALSE), """")"
Range("ae4").Formula = "=IFERROR(VLOOKUP($Y4,$C$7:$AC$100,26,FALSE), """")"
Range("af2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$T$100,13,FALSE), """")"
Range("af3").Formula = "=IFERROR(VLOOKUP($Y3,$C$7:$T$100,13,FALSE), """")"
Range("af4").Formula = "=IFERROR(VLOOKUP($Y4,$C$7:$T$100,13,FALSE), """")"
Range("ag2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$AC$100,27,FALSE), """")"
Range("ag3").Formula = "=IFERROR(VLOOKUP($Y3,$C$7:$AC$100,27,FALSE), """")"
Range("ag4").Formula = "=IFERROR(VLOOKUP($Y4,$C$7:$AC$100,27,FALSE), """")"
  '-----------------------------------------------
   Range("B3").Select
   MsgBox "Process Complete" & vbNewLine & _
        crow - 7 & " Records found"
        'reset find variable
    Range("B3").Select
 

End


        End With
        
    




End Sub









' hide all sheets apart from the lookup
Sub HideAllSheetsBarOne()
Dim sht As Object

For Each sht In Sheets
If sht.Name <> "Lookup" Then
sht.Visible = xlSheetHidden
End If
Next sht

End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Your code to work on the sheets needs to go in the Else section of the If statement.
 
Upvote 0
Solution
Thank you @Fluff I got it to work but then it was only looking at that specific sheet and excluding everything else.

Based on what you said I got it to work but used the below code

VBA Code: 
      For Each wSht In ThisWorkbook.Worksheets
  
      If wSht.Name <> "Lookup" And wSht.Name <> "Sheet14" Then
       Debug.Print wSht.Name

Thanks again for your help, you guys are amazing!
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Pasting string - variable in cell - ERROR
Replies
9
Views
529
Alex Blakenburg
A
S
  • Question Question
How to get VBA search to return the row of data if the last cell in the range has no data in it
Replies
1
Views
885
Saurabhj
Saurabhj
R
  • Question Question
VBA - AUTO FIT, Can we remove make the sum formula in below code
Replies
1
Views
157
Joe4
Joe4
K
  • Question Question
VBA DOUBT- can we add heading color and remove blank columns in this code?
Replies
0
Views
162
Kishore1982
K
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
584
RoryA
RoryA

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top