Cause of Method 'ShowAllData' of object '_worksheet Error

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have just recently started running into an error with my code. I cannot recall being burdened with it for the past month working with it, but it started cropping up yesterday as I was running my VBA application.

Below is a procedure that I use to submit user edited data from a worksheet based form (not a userform) from a button click. The data from the cells holding the data is placed into a database on another worksheet.

Rich (BB code):
Sub submit1()

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Dim wshmain As Worksheet 'main
    Dim wshvar As Worksheet 'varhold
    Dim wshcore As Worksheet 'control_1
    Dim wshgrp As Worksheet 'group_data
    
    Dim rnum1 As Long 'row number of current RID
    Dim End_RowDest As Long
    
    Dim lastrow As Integer
    Dim nonval As Integer
    Dim iRet As Integer
    Dim pret As Integer
    Dim ERC2 As Integer
    
    Dim strtype As String 'D(iamond), F(ield), C(ourt)
    Dim altstff As String
    
    Dim lurange1 As Range
    Dim rngstaff As Range 'staff name:crew
    Dim rngstaff2 As Range 'staff main grid

    Set wshmain = Worksheets("Main")
    Set wshvar = Worksheets("varhold")
    Set wshcore = Worksheets("CONTROL_1")
    Set wshgrp = Worksheets("Group_Data")
    
    Set lurange1 = wshcore.Range("CW:DE")
    Set rngstaff = Worksheets("Staff").Range("L4:M20")
    Set rngstaff2 = Worksheets("Staff").Range("A4:E19")
    
    rnum1 = Application.Match(wshmain.Range("B14"), wshcore.Range("A:A")) 'determine row number in control_1 for which RID matches RID on form

    With wshcore
        If .FilterMode Then .ShowAllData
        
        .Range("F" & rnum1) = wshmain.Range("L18") 'Program
        .Range("G" & rnum1) = wshmain.Range("Y18") 'Customer
        .Range("DY" & rnum1) = wshmain.Range("Y18").Value & " (" & wshmain.Range("AH18").Value & ")"
        .Range("Q" & rnum1) = 1 'set reviewed flag
                
        strtype = Left(wshmain.Range("I18"), 1)
        
        If strtype = "D" Then
        
        'General & staff
        
            .Range("R" & rnum1) = Left(wshmain.Range("X25"), 2) 'RESP
            .Range("S" & rnum1) = wshmain.Range("X25") 'SUBRESP (Signature)
            .Range("CW" & rnum1) = .Range("E" & rnum1) + .Range("S" & rnum1) 'type+subresp (DRRPL1)
            .Range("DI" & rnum1) = Format(Application.VLookup(.Range("S" & rnum1), rngstaff2, 4, False), "h:mm AM/PM") 'primary crew start
            .Range("DJ" & rnum1) = Format(Application.VLookup(.Range("S" & rnum1), rngstaff2, 5, False), "h:mm AM/PM") 'primary crew end
            
            altstaff = Left(.Range("S" & rnum1), 3) & "2"
        
            .Range("DF" & rnum1) = Application.VLookup(altstaff, rngstaff2, 2, False) 'alternate crew start
            .Range("DG" & rnum1) = Format(Application.VLookup(altstaff, rngstaff2, 4, False), "h:mm AM/PM") 'alternate crew start
            .Range("DH" & rnum1) = Format(Application.VLookup(altstaff, rngstaff2, 5, False), "h:mm AM/PM")  'alternate crew start
            
        'Grooming
            .Range("T" & rnum1) = Format(wshmain.Range("D24"), "dd-mmm") 'Date
            .Range("U" & rnum1) = wshmain.Range("D25") 'Quantifier
            .Range("V" & rnum1) = Format(wshmain.Range("E25"), "h:mm AM/PM") 'Time
            .Range("W" & rnum1) = wshmain.Range("D25") & Format(wshmain.Range("E25"), "h:mm AM/PM") 'Report time
            .Range("X" & rnum1) = wshmain.Range("D26") 'Name
            .Range("Y" & rnum1) = wshmain.Range("G26") 'Crew
            
            'gm_doit (DL)
            If .Range("T" & rnum1) = "Not Available (NA)" Then
                .Range("DL" & rnum1) = "NA"
            ElseIf Range("T" & rnum1) = "Not Required (NR)" Then
                .Range("DL" & rnum1) = "NR"
            Else
                .Range("DL" & rnum1) = "Yes"
            End If
            
        'Preparation
            .Range("Z" & rnum1) = wshmain.Range("K27") 'Type
            .Range("AA" & rnum1) = Format(wshmain.Range("K24"), "dd-mmm") 'Date
            .Range("AB" & rnum1) = wshmain.Range("K25") 'Quantifier
            .Range("AC" & rnum1) = Format(wshmain.Range("L25"), "h:mm AM/PM") 'Time
            .Range("AD" & rnum1) = wshmain.Range("K25") & Format(wshmain.Range("L25"), "h:mm AM/PM") 'Report time
            .Range("AE" & rnum1) = wshmain.Range("K26") 'Name
            .Range("AF" & rnum1) = wshmain.Range("N26") 'Crew
            If .Range("AA" & rnum1) = "Not Available (NA)" Then 'padvdate
                .Range("DK" & rnum1) = "NO"
            ElseIf .Range("AA" & rnum1) = "Not Required (NR)" Then 'padvdate
                .Range("DK" & rnum1) = "NR"
            Else
                .Range("DK" & rnum1) = "Yes"
            End If
            
        'Setup
            .Range("AG" & rnum1) = wshmain.Range("K29") 'Bases
            .Range("AH" & rnum1) = wshmain.Range("P29") 'Safebase
            .Range("AI" & rnum1) = wshmain.Range("P31") 'Mat
            .Range("AJ" & rnum1) = wshmain.Range("K30") 'Pitching
            '.Range("AK" & rnum1) = wshmain.Range("K24") 'EMPTY
            .Range("AL" & rnum1) = wshmain.Range("P30") 'Pitch Circle
            .Range("AM" & rnum1) = wshmain.Range("K31") 'Batters Box
            .Range("AN" & rnum1) = wshmain.Range("K33") 'Coaches Box
            .Range("AO" & rnum1) = wshmain.Range("P32") 'Runline
            .Range("AP" & rnum1) = wshmain.Range("K32") 'Safeline
            .Range("AQ" & rnum1) = wshmain.Range("P33") 'Commit line
            .Range("AU" & rnum1) = wshmain.Range("K34") 'Other1
            .Range("AV" & rnum1) = wshmain.Range("K35") 'Other2
            
        'Signature
            .Range("AW" & rnum1) = wshmain.Range("U24") 'Quantifier
            .Range("AX" & rnum1) = Format(wshmain.Range("V24"), "h:mm AM/PM") 'Time
            .Range("AY" & rnum1) = wshmain.Range("U24") & Format(wshmain.Range("V24"), "h:mm AM/PM") 'Report time
            .Range("AZ" & rnum1) = wshmain.Range("U25") 'Name
            .Range("BA" & rnum1) = wshmain.Range("X25") 'Crew
            .Range("DZ" & rnum1) = Application.VLookup(.Range("AZ" & rnum1), rngstaff, 2, False) 'key1 (crew ID)
            If .Range("AW" & rnum1) = "NA" Or .Range("AW" & rnum1) = "NR" Then
                .Range("DM" & rnum1) = .Range("V" & rnum1) 'sig_doit
            Else
                .Range("DM" & rnum1) = "Yes" 'sig_doit
            End If
            
        'Lights
            .Range("DO" & rnum1) = wshmain.Range("AB24") 'Quantifier
            .Range("BB" & rnum1) = Format(wshmain.Range("AC24"), "h:mm AM/PM") 'Time
            .Range("DP" & rnum1) = wshmain.Range("AB24") & Format(wshmain.Range("AC24"), "h:mm AM/PM") 'Report time
            .Range("BC" & rnum1) = wshmain.Range("AB25") 'Name
            .Range("BD" & rnum1) = wshmain.Range("AE25") 'Crew
            
            .Range("DQ" & rnum1) = wshmain.Range("AB26") 'Quantifier
            .Range("BE" & rnum1) = Format(wshmain.Range("AC26"), "h:mm AM/PM") 'Time
            .Range("DR" & rnum1) = wshmain.Range("AB26") & Format(wshmain.Range("AC26"), "h:mm AM/PM") 'Report time
            .Range("BF" & rnum1) = wshmain.Range("AB27") 'Name
            .Range("BG" & rnum1) = wshmain.Range("AE27") 'Crew
            If .Range("DP" & rnum1) = "NA" Or .Range("DP" & rnum1) = "NR" Then
                .Range("DN" & rnum1) = .Range("DP" & rnum1)
            Else
                .Range("DN" & rnum1) = "Yes"
            End If
        
        'Closing
            .Range("DT" & rnum1) = Format(wshmain.Range("AI24"), "d-MMM") 'Date
            .Range("BH" & rnum1) = wshmain.Range("AI25") 'Quantifier
            .Range("BI" & rnum1) = Format(wshmain.Range("AJ25"), "h:mm AM/PM") 'Time
            .Range("BJ" & rnum1) = wshmain.Range("AI25") & Format(wshmain.Range("AJ25"), "h:mm AM/PM") 'Report time
            .Range("BK" & rnum1) = wshmain.Range("AI26") 'Name
            .Range("BL" & rnum1) = wshmain.Range("AL26") 'Crew
            If .Range("DT" & rnum1) = "Not Available(NA)" Then
                .Range("DS" & rnum1) = "NA"
            ElseIf .Range("DT" & rnum1) = "Not Required (NR)" Then
                .Range("DS" & rnum1) = "NR"
            Else
                .Range("DS" & rnum1) = "Yes"
            End If
        
            If .Range("I18") = "DT" Then
            
            'RELINE 1
                .Range("BN" & rnum1) = Format(wshmain.Range("I40"), "h:mm AM/PM") 'From
                .Range("BP" & rnum1) = Format(wshmain.Range("I41"), "h:mm AM/PM") 'To
                .Range("BQ" & rnum1) = Format(wshmain.Range("I40"), "h:mm AM/PM") & " - " & Format(wshmain.Range("I41"), "h:mm AM/PM") 'Envelope
                .Range("BR" & rnum1) = wshmain.Range("I38") 'Type (R1RelineType)
                If .Range("BR" & rnum1) = "Not Required (NR)" Then
                    .Range("BM" & rnum1) = "NR" 'rel1_doit
                    .Range("BO" & rnum1) = "NR" 'Division
                Else 'RELINE / CHANGEOVER
                    .Range("DM" & rnum1) = "Yes"
                    .Range("BO" & rnum1) = wshmain.Range("I39") 'Division
                End If
                .Range("BS" & rnum1) = wshmain.Range("I42") 'Name
                .Range("BT" & rnum1) = wshmain.Range("K42") 'Crew
                                
            'RELINE 2
                .Range("BV" & rnum1) = Format(wshmain.Range("P40"), "h:mm AM/PM") 'From
                .Range("BX" & rnum1) = Format(wshmain.Range("P41"), "h:mm AM/PM") 'To
                .Range("BQ" & rnum1) = Format(wshmain.Range("P40"), "h:mm AM/PM") & " - " & Format(wshmain.Range("P41"), "h:mm AM/PM") 'Envelope
                .Range("BZ" & rnum1) = wshmain.Range("P38") 'Type(R2RelineType)
                If .Range("BZ" & rnum1) = "Not Required (NR)" Then
                    .Range("BU" & rnum1) = "NR" 'rel2_doit
                    .Range("BW" & rnum1) = "NR" 'Division
                Else 'RELINE / CHANGEOVER
                    .Range("BU" & rnum1) = "Yes"
                    .Range("BW" & rnum1) = wshmain.Range("P39") 'Division
                End If
                .Range("CA" & rnum1) = wshmain.Range("P42") 'Name
                .Range("CB" & rnum1) = wshmain.Range("R42") 'Crew
                
            'RELINE 3
                .Range("CD" & rnum1) = Format(wshmain.Range("W40"), "h:mm AM/PM") 'From
                .Range("CF" & rnum1) = Format(wshmain.Range("W41"), "h:mm AM/PM") 'To
                .Range("CG" & rnum1) = Format(wshmain.Range("W40"), "h:mm AM/PM") & " - " & Format(wshmain.Range("W41"), "h:mm AM/PM") 'Envelope
                .Range("CH" & rnum1) = wshmain.Range("W38") 'Type (R3RelineType)
                If .Range("CH" & rnum1) = "Not Required (NR)" Then
                    .Range("CC" & rnum1) = "NR" 'rel3_doit
                    .Range("CE" & rnum1) = "NR" 'Division
                Else 'RELINE / CHANGEOVER
                    .Range("CC" & rnum1) = "Yes"
                    .Range("CE" & rnum1) = wshmain.Range("W39") 'Division
                End If
                .Range("CI" & rnum1) = wshmain.Range("W42") 'Name
                .Range("CJ" & rnum1) = wshmain.Range("Y42") 'Crew
                
            'RELINE 4
                .Range("CN" & rnum1) = Format(wshmain.Range("AD40"), "h:mm AM/PM") 'From
                .Range("CL" & rnum1) = Format(wshmain.Range("AD41"), "h:mm AM/PM") 'To
                .Range("CO" & rnum1) = Format(wshmain.Range("AD40"), "h:mm AM/PM") & " - " & Format(wshmain.Range("AD41"), "h:mm AM/PM") 'Envelope
                .Range("CP" & rnum1) = wshmain.Range("AD38") 'Type
                If .Range("CP" & rnum1) = "Not Required (NR)" Then
                    .Range("CK" & rnum1) = "NR" 'rel3_doit
                    .Range("CM" & rnum1) = "NR" 'Division
                Else 'RELINE / CHANGEOVER
                    .Range("CK" & rnum1) = "Yes"
                    .Range("CM" & rnum1) = wshmain.Range("AD39") 'Division
                End If
                .Range("CQ" & rnum1) = wshmain.Range("AD42") 'Name
                .Range("CR" & rnum1) = wshmain.Range("AF42") 'Crew
            End If
        
        ElseIf strtype = "F" Then
            .Range("R" & rnum1) = Left(wshmain.Range("G47"), 2) 'RESP
            .Range("S" & rnum1) = wshmain.Range("G47") 'SUBRESP (Signature)
            .Range("CW" & rnum1) = .Range("E" & rnum1) + .Range("S" & rnum1)
            .Range("DI" & rnum1) = Format(Application.VLookup(.Range("S" & rnum1), rngstaff2, 4, False), "h:mm AM/PM") 'primary crew start
            .Range("DJ" & rnum1) = Format(Application.VLookup(.Range("S" & rnum1), rngstaff2, 5, False), "h:mm AM/PM") 'primary crew end
            altstaff = Left(.Range("S" & rnum1), 3) & "2"
            .Range("DF" & rnum1) = Application.VLookup(altstaff, rngstaff2, 2, False) 'primary crew start
            .Range("DG" & rnum1) = Format(Application.VLookup(altstaff, rngstaff2, 4, False), "h:mm AM/PM") 'primary crew start
            .Range("DH" & rnum1) = Format(Application.VLookup(altstaff, rngstaff2, 5, False), "h:mm AM/PM")  'primary crew start
                
        'Signature
            .Range("AW" & rnum1) = wshmain.Range("D46") 'Quantifier
            .Range("AX" & rnum1) = Format(wshmain.Range("E46"), "h:mm AM/PM") 'Time
            .Range("AY" & rnum1) = wshmain.Range("D46") & Format(wshmain.Range("E46"), "h:mm AM/PM") 'Report time
            .Range("AZ" & rnum1) = wshmain.Range("D47") 'Name
            .Range("BA" & rnum1) = wshmain.Range("G47") 'Crew
            .Range("AR" & rnum1) = wshmain.Range("Q46")
            .Range("AS" & rnum1) = wshmain.Range("Q49")
            .Range("DZ" & rnum1) = Application.VLookup(.Range("AZ" & rnum1), rngstaff, 2, False) 'key1 (crew ID)
            If .Range("AW" & rnum1) = "NA" Or .Range("AW" & rnum1) = "NR" Then
                .Range("DM" & rnum1) = .Range("V" & rnum1) 'sig_doit
            Else
                .Range("DM" & rnum1) = "Yes" 'sig_doit
            End If
            
        'Lights
            .Range("DO" & rnum1) = wshmain.Range("K46") 'Quantifier
            .Range("BB" & rnum1) = Format(wshmain.Range("L46"), "h:mm AM/PM") 'Time
            .Range("DP" & rnum1) = wshmain.Range("K46") & Format(wshmain.Range("L46"), "h:mm AM/PM") 'Report time
            If .Range("H" & rnum1) = "RIM Park Outdoor Facilities" And Left(.Range("BB" & rnum1), 1) <> "N" Then
                .Range("BC" & rnum1) = "AUTO"
            Else
                .Range("BC" & rnum1) = wshmain.Range("K47") 'Name
            End If
            .Range("BD" & rnum1) = wshmain.Range("N47") 'Crew
            
            .Range("DQ" & rnum1) = wshmain.Range("K48") 'Quantifier
            .Range("BE" & rnum1) = Format(wshmain.Range("L48"), "h:mm AM/PM") 'Time
            .Range("DR" & rnum1) = wshmain.Range("K48") & Format(wshmain.Range("L48"), "h:mm AM/PM") 'Report time
            If .Range("H" & rnum1) = "RIM Park Outdoor Facilities" And Left(.Range("BB" & rnum1), 1) <> "N" Then
                .Range("BF" & rnum1) = "AUTO"
            Else
                .Range("BF" & rnum1) = wshmain.Range("K49") 'Name
            End If
            .Range("BG" & rnum1) = wshmain.Range("N49") 'Crew
            If .Range("DP" & rnum1) = "NA" Or .Range("DP" & rnum1) = "NR" Then
                .Range("DN" & rnum1) = .Range("DP" & rnum1)
            Else
                .Range("DN" & rnum1) = "Yes"
            End If
            
    
        Else 'courts
            .Range("R" & rnum1) = Left(wshmain.Range("N53"), 2) 'RESP
            .Range("S" & rnum1) = wshmain.Range("N53") 'SUBRESP (Signature)
            .Range("CW" & rnum1) = .Range("E" & rnum1) + .Range("S" & rnum1)
            .Range("DI" & rnum1) = Format(Application.VLookup(.Range("S" & rnum1), rngstaff2, 4, False), "h:mm AM/PM") 'primary crew start
            .Range("DJ" & rnum1) = Format(Application.VLookup(.Range("S" & rnum1), rngstaff2, 5, False), "h:mm AM/PM") 'primary crew end
            altstaff = Left(.Range("S" & rnum1), 3) & "2"
            .Range("DF" & rnum1) = Application.VLookup(altstaff, rngstaff2, 2, False) 'primary crew start
            .Range("DG" & rnum1) = Format(Application.VLookup(altstaff, rngstaff2, 4, False), "h:mm AM/PM") 'primary crew start
            .Range("DH" & rnum1) = Format(Application.VLookup(altstaff, rngstaff2, 5, False), "h:mm AM/PM")  'primary crew start
        
        'Preparation
            .Range("AA" & rnum1) = wshmain.Range("D52") 'Date
            .Range("AB" & rnum1) = wshmain.Range("D53") 'Quantifier
            .Range("AC" & rnum1) = Format(wshmain.Range("E53"), "h:mm AM/PM") 'Time
            .Range("AD" & rnum1) = wshmain.Range("D53") & Format(wshmain.Range("E53"), "h:mm AM/PM") 'Report time
            .Range("AE" & rnum1) = wshmain.Range("D54") 'Name
            .Range("AF" & rnum1) = wshmain.Range("G54") 'Crew
            If .Range("AA" & rnum1) = "Not Available (NA)" Then 'padvdate
                .Range("DK" & rnum1) = "NO"
            ElseIf .Range("AA" & rnum1) = "Not Required (NR)" Then 'padvdate
                .Range("DK" & rnum1) = "NR"
            Else
                .Range("DK" & rnum1) = "Yes"
            End If
            
        'Signature
            .Range("AW" & rnum1) = wshmain.Range("K52") 'Quantifier
            .Range("AX" & rnum1) = Format(wshmain.Range("L52"), "h:mm AM/PM") 'Time
            .Range("AY" & rnum1) = wshmain.Range("K52") & Format(wshmain.Range("L52"), "h:mm AM/PM") 'Report time
            .Range("AZ" & rnum1) = wshmain.Range("K53") 'Name
            .Range("BA" & rnum1) = wshmain.Range("N53") 'Crew
            .Range("DZ" & rnum1) = Application.VLookup(.Range("AZ" & rnum1), rngstaff, 2, False) 'key1 (crew ID)
            If .Range("AW" & rnum1) = "NA" Or .Range("AW" & rnum1) = "NR" Then
                .Range("DM" & rnum1) = .Range("V" & rnum1) 'sig_doit
            Else
                .Range("DM" & rnum1) = "Yes" 'sig_doit
            End If
        
        End If
        
        lr1 = .Range("A" & Rows.Count).End(xlUp).Row
        
        For i = 2 To lr1
            crewstr = Right(.Range("CW" & i), 4)
            .Range("CX" & i) = Application.CountIf(.Range("CW2:CW" & lr1), "DR" + crewstr)
            .Range("CY" & i) = Application.CountIf(.Range("CW2:CW" & lr1), "DT" + crewstr)
            .Range("CZ" & i) = Application.CountIf(.Range("CW2:CW" & lr1), "FR" + crewstr)
            .Range("DA" & i) = Application.CountIf(.Range("CW2:CW" & lr1), "FT" + crewstr)
            .Range("DB" & i) = Application.CountIf(.Range("CW2:CW" & lr1), "CR" + crewstr)
            .Range("DC" & i) = Application.CountIf(.Range("CW2:CW" & lr1), "CT" + crewstr)
            .Range("DD" & i) = .Range("CX" & i) + .Range("CY" & i)
            .Range("DE" & i) = .Range("CZ" & i) + .Range("DA" & i) + .Range("DB" & i) + .Range("DC" & i)
        Next i
    
        Call display_counts
        
    End With
    MsgBox "Record successfully submitted."
'
    If wshvar.Range("A40") = 3 Then
        pret = MsgBox("Would you like to submit this rental to the group database?", 36, "New Rental Information")
        If pret = vbYes Then
            With wshgrp
                If .FilterMode Then .ShowAllData
                ERC2 = (.Range("A" & Rows.Count).End(xlUp).Row) + 1
                .Range("A" & ERC2) = wshmain.Range("B18") 'contract #
                .Range("B" & ERC2) = wshmain.Range("F18")
                .Range("C" & ERC2) = "" 'ammend name
                .Range("D" & ERC2) = "rental " & wshmain.Range("B18") & ".pdf"
                .Range("E" & ERC2) = wshmain.Range("I18") 'type
                .Range("F" & ERC2) = wshmain.Range("L18") 'event
                .Range("G" & ERC2) = "" 'org
                .Range("H" & ERC2) = "" 'calibre
                .Range("I" & ERC2) = wshmain.Range("Y18") 'customer(primary)name
                .Range("J" & ERC2) = wshmain.Range("AH18") 'primary telephone
            
                If Left(wshmain.Range("I18"), 1) = "D" Then
                    .Range("M" & ERC2) = wshmain.Range("K29") 'bases
                    .Range("N" & ERC2) = wshmain.Range("P29") 'safebase
                    .Range("O" & ERC2) = wshmain.Range("P31") 'mat
                    .Range("P" & ERC2) = wshmain.Range("K30") 'pitching
                    .Range("R" & ERC2) = wshmain.Range("P30") 'circle
                    .Range("S" & ERC2) = wshmain.Range("K31") 'batbox
                    .Range("T" & ERC2) = wshmain.Range("K33") 'coachbox
                    .Range("U" & ERC2) = wshmain.Range("P32") 'runline
                    .Range("V" & ERC2) = wshmain.Range("K32") 'safeline
                    .Range("W" & ERC2) = wshmain.Range("P33") 'commit
                ElseIf Left(wshmain.Range("I18"), 1) = "F" Then
                    .Range("X" & ERC2) = wshmain.Range("Q46") 'fld layout
                    .Range("Y" & ERC2) = wshmain.Range("Q49") 'fld goals
                Else
                End If
                .Range("AA" & ERC2) = wshmain.Range("K34") 'other1
                .Range("AB" & ERC2) = wshmain.Range("K35") 'other2
                .Range("AC" & ERC2) = Format(Date, "dd-mmm-yy")
                .Range("E" & ERC2) = wshmain.Range("I18")
                ERC2 = (.Range("A" & Rows.Count).End(xlUp).Row) + 1
                .Range("A2:AC" & ERC2).sort key1:=.Range("A1"), order1:=xlAscending, Header:=xlYes
            End With
            MsgBox wshmain.Range("B18") & " was successfully added to the group database."
        Else
            MsgBox "Submission Declined."
        End If
    End If

'
    End_RowDest = wshcore.Range("Q" & Rows.Count).End(xlUp).Row
    nonval = Application.CountIf(wshcore.Range("Q2:Q" & End_RowDest), 0)
    With wshmain
        .Unprotect
        .Range("D4").Value = nonval
        .Range("D5").Value = Application.CountIf(wshcore.Range("Q2:Q" & End_RowDest), 1)
        .Protect
    End With
    If nonval = 0 Then 'All records have been confirmed
        iRet = MsgBox(("All records have been reviewed." & Chr(13) & "Do you wish to reconcile the database at this time?"), 4, "Reconciliation")
        If iRet = vbYes Then
            Call reconcile1
        End If
    End If
    rid_forward

    Application.EnableEvents = True

End Sub

The line highlighted in red is giving me a 'Method 'ShowAllData' of object '_worksheet' failed" error, and I have no idea why. The worksheet wshcore, referring to worksheet("CONTROL_1") exists in the workbook, and is unprotected. Any suggestions?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi Andrew.

Yes, it is unprotected. I checked it again just to make sure. I even put an .activate and .unprotect line in the code prior the filtermode statement.
 
Upvote 0
I found the problem. The autofilter feature had been enabled on that worksheet.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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