Excel VBA Run-Time Error 1004

KathrynAlty

New Member
Joined
Jan 22, 2016
Messages
6
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
Is there data in column B on the active sheet?

Does this work?
Code:
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
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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