Excel VBA Run-Time Error 1004

[KathrynAlty]

I wonder if anyone might be able to help me?

I've come across an error in a workbook set up for the 2016 year (it was a direct copy of the 2015 original, clearing previous data and updating the date range).

When I run the macro in the 2016 file, I am presented with the following error: "Run-time error '1004': Application-defined or object-defined error" on the bold red line below.

VBA Code:
Sub Filter_Copy()

Application.ScreenUpdating = False

msg1 = MsgBox("Filter and Copy data. Are you sure?", vbYesNo)
If msg1 = vbNo Then Exit Sub

ActiveSheet.Unprotect

Sheets("Front").Select

Range("F:G").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Sheets("Case Data").Select

Range("B3").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Front").Select

ActiveSheet.ShowAllData

Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Selection.ClearContents


Range("B4").Select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Application.ScreenUpdating = True

msg1 = MsgBox("All data filtered and copied", vbOKOnly)


End Sub

I've read through a lot of these forums regarding this exact error but can't seem to find a solution that works! Hoping that someone can point me in the right direction (apologies if this has been answered before!).

Thank you in advance.

 

[Jonmo1]

If column B is empty (at least from B4 and down), then this line
Range("B3").End(xlDown)
Will go all the way to the last row of the sheet
Then attempting to offset 1 more row from that is an error, because there are no more rows available.

If you're basically wanting to paste in the cell below the last used cell in column B
It's generally better to start from the end of the sheet, and look UP

Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select

 

[Norie]

Is there data in column B on the active sheet?

Does this work?

Sub Filter_Copy()
Dim rng As Range
Dim msg1 As VbMsgBoxResult

    Application.ScreenUpdating = False

    msg1 = MsgBox("Filter and Copy data. Are you sure?", vbYesNo)

    If msg1 = vbNo Then Exit Sub

    With Sheets("Front")
    
        .Unprotect
        
        .Range("F:G").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

        Set rng = .Range(.Range("B4"), .Range("B4").End(xlDown).End(xlToRight))

        rng.Copy

        Sheets("Case Data").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        
        .ShowAllData

        rng.ClearContents

        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        
    End With

    Application.ScreenUpdating = True

    msg1 = MsgBox("All data filtered and copied", vbOKOnly)

End Sub

 

[KathrynAlty]

It worked!

Thank you very much for your help.

If you have time - are you able to explain what was going wrong in the original code?

I've been looking at it all afternoon to no avail!

Thank you again for your help