VBA to replace existing data with macro to copy rows based on cell value to new sheet

  • Firstly, I would like to apologise if this has already been resolved in previous thread(s). I have searched with no success.


    BACKGROUND
    I have a Workbook called: Company Documents Register, which as the name states, is a Register of all Company Documents.
    Within this workbook, I have the following worksheets:
    MASTER (sheet1)
    HACCP (sheet2)


    MASTER
    Is used for Data Entry. All document info is entered into this worksheet only.


    The following code is assigned to a Command Button to copy all rows that contain the cell value 'haccp'.


    This is working perfectly.....with one exception.


    ISSUE TO BE RESOLVED


    Currently, each time the macro is run, it copies all filtered results and pastes them onto sheet2 as new entries however I require it to replace all data beginning at row 2 (as row 1 contains headings). Is it possible to have sheet2 data replaced each time the macro is run?
    I am currently deleting the data from sheet2 prior to clicking the command button as to not duplicate each entry on sheet2.


    Thanks in advance for your time, please let me know if you require any clarification or further info..

  • Try:

    Code
    Private Sub Command_Click()
      
        Application.ScreenUpdating = False
        Sheet2.UsedRange.Offset(1).Clear
        Columns(3).AutoFilter 1, "haccp"
        Range("a2", Range("h" & Rows.Count).End(3)).Copy Sheet2.[a2]
        Columns(3).AutoFilter
      
    End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!