Compile Error: Invalid or Unqualified Reference ????

[jwb1012]

Hi - I cant seem to figure out why I am getting this "Compile Error: Invalid or Unqualified Reference" on the bold/italicized/underlined line of code below. Any thoughts????

Specific line:
With .Cells(Lrow, "L")

Full Code:

Sub Consolidate()
    Dim wb As ThisWorkbook
    Dim Sh As Worksheet
    Dim CopyRng As Range
    Dim Start_Row As Integer
    Dim End_Row As Integer
    Dim Dest_Sh As Worksheet
    Dim Dest_Lastrow As Integer
    Dim Dest_Firstrow As Integer
    Dim Lrow As Long
        
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With    '(1.) Ensures "Export - Labor BOEs" worksheet is visible
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Export - Labor BOEs").Visible = True
    Sheets("Export - Labor BOEs").Activate
    On Error GoTo 0
    Application.DisplayAlerts = True
        
    
        '(2.) Delete data on "Export - Labor BOEs" worksheet...beginning in row 2
        Set Dest_Sh = Sheets("Export - Labor BOEs")
        
            'Sets the 'Dest_LastRow' variable based on the last row in Column L
            Dest_Lastrow = Dest_Sh.Cells(Rows.Count, "L").End(xlUp).Row
        
            'If the 'Dest_LastRow' variable is 2 or greater then...
            If Dest_Lastrow >= 3 Then
        
            '...clear the contents from Row 2 to the last row
            Dest_Sh.Rows("3:" & Dest_Lastrow).Delete
            
        End If
    
       '(3.) Loop through all worksheets beginning with Labor BOE
        For Each Sh In ActiveWorkbook.Sheets
            If Left(Sh.Name, 9) = "Labor BOE" Then
                End_Row = Sh.Range("L" & Rows.Count).End(xlUp).Row
                Start_Row = Dest_Sh.Range("L" & Rows.Count).End(xlUp).Row + 1
            
                'Set the range that you want to copy
                Set CopyRng = Sh.Range("A2", "L" & End_Row)
                'This example copies values/formats
                CopyRng.Copy
                With Dest_Sh.Range("A" & Start_Row)
                    .PasteSpecial 8    ' Column width
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With
            End If
        Next Sh
    
    '(4.) Delete all rows that say "DELETE" in column L
    Dest_Sh.Activate
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Dest_Lastrow To 3 Step -1
            'We check the values in the L column in this example
         [U][I][B]   With .Cells(Lrow, "L")[/B][/I][/U]
                If Not IsError(.Value) Then
                    If .Value = "DELETE" Then .EntireRow.Delete
                    'This will delete each row with the Value "delete"
                    'in Column L, case sensitive.
                End If
            End With
        Next Lrow
        
        
ExitTheSub:
    Application.GoTo Dest_Sh.Cells(1)
    ActiveWindow.DisplayGridlines = False
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

 

[korhan]

Not sure if you found a solution but how about changing

With .Cells(Lrow, "L") to 
With ActiveSheet.Cells(Lrow, "L")

 

[StephenCrump]

Not sure if you found a solution but how about changing

With .Cells(Lrow, "L") to 
With ActiveSheet.Cells(Lrow, "L")

Or better still, qualify with the worksheet name:

With Dest_Sh.Cells(Lrow, "L")

and get rid of this line:

Dest_Sh.Activate

And rather than looping through each row, it would be faster (especially if you have lots of data) to:

- Filter for "DELETE" in column L
- Bulk delete those rows
- Remove filter

 

[jwb1012]

No, I still can't find a solution! So thank you very much.

Unfortunately, when I do this it results in a Run Time Error '9': Subscript out of range

It highlights this row:


'(2.) Delete data on "Export - Labor BOEs" worksheet...beginning in row 2
Set Dest_Sh = Sheets("Export - Labor BOEs")


Any thoughts???

 

[StephenCrump]

Run Time Error '9': Subscript out of range

The error means there is no sheet called "Export - Labor BOEs" in the ActiveWorkbook.

If you think that you do have this sheet name, check that the name is an exact match, e.g. no leading or trailing spaces, double spaces etc?