Macro is deleting sheet, and I would like it to clear contents

I am using a borrowed macro to autofilter for me. The macro does exactly what I want it to, with one exception. It deletes and recreates a worksheet page everytime you run it. I think I would like it to just clear contents instead of delete. I am trying to copy the data from the filter to an invoice sheet, but it keeps returning a #REF! error because the sheet is deleted and recreated each time I run the macro. I tried changing the .delete to a .clearcontents, but then it has a problem with creating a sheet with a name that is already being used. I'm rather new at macros and need some help with this. Thanks....The macro is:

Sub Copy_With_AutoFilter1()
    Dim WS As Worksheet
    Dim WSNew As Worksheet
    Dim rng As Range
    Dim rng2 As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Name of the worksheet with the data
    Set WS = Sheets("Order Sheet")  '<<< Change

    'Set filter range : A1 is the top left cell of your filter range and
    'the header of the first column, D is the last column in the filter range
    Set rng = WS.Range("A1:C" & Rows.Count)

    'Firstly, remove the AutoFilter
    WS.AutoFilterMode = False

    'Delete the sheet MyFilterResult if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("MyFilterResult").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    'Add a new worksheet to copy the filter results in
    Set WSNew = Worksheets.Add
    WSNew.Name = "MyFilterResult"

    'Copy the visible data and use PasteSpecial to paste to the new worksheet
    WS.AutoFilter.Range.Copy
    With WSNew.Range("A1")
        ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
        .PasteSpecial Paste:=8
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
        .Select
    End With
    
    'Close AutoFilter
    WS.AutoFilterMode = False

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I will add them this time but read the rules before you post again

You must have the sheet("MyFilterResult") to copy to

Sub Copy_With_AutoFilter1()
    Dim WS As Worksheet
    Dim WSNew As Worksheet
    Dim rng As Range
    Dim rng2 As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Name of the worksheet with the data
    Set WS = Sheets("Order Sheet")  '<<< Change

    'Set filter range : A1 is the top left cell of your filter range and
    'the header of the first column, D is the last column in the filter range
    Set rng = WS.Range("A1:C" & Rows.Count)

    'Firstly, remove the AutoFilter
    WS.AutoFilterMode = False

    'Delete the sheet MyFilterResult if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("MyFilterResult").UsedRange.ClearContents
    Application.DisplayAlerts = True
    On Error GoTo 0

   

    'Copy the visible data and use PasteSpecial to paste to the new worksheet
    WS.AutoFilter.Range.Copy
    With WSNew.Range("A1")
        ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
        .PasteSpecial Paste:=8
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
        .Select
    End With
    
    'Close AutoFilter
    WS.AutoFilterMode = False

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

I don't think you copied all the relevant sections of Roy's code. You're still trying to create the worksheet again.

It's a help to say what line the error occurs on.