Need help adding a clear sheet to a VBA

[mattbird]

Hi I using a spreadsheet that pulls information from one sheet table to another sheet table. The only issue is it overlays the info, which is ok if the 'Apr' sheet has more info to copy than what is already on the 'Mask FIT Matrix' sheet. However if there is less info to copy from the 'Apr' sheet, it only overlays this info in the 'Mask FIT Matrix' sheet, leaving the remaining old data behind.

Example: If the 'Mask FIT Matrix' sheet has 10 row of info in the table and the 'Apr' sheet has 20 row it copies this info over the top which is great. but if there were only 5 row of info in the 'Apr' sheet it would copy over this info but there would still be 5 row of old info remaining on the 'Mask FIT Matrix' sheet.

How would I edit the code to clear the 'Mask FIT Matrix' sheet before the new info is copied from the 'Apr' sheet and pasted into the 'Mask FIT Matrix' sheet?

I hope this makes sense?

Sub Apr_Click()

Dim head_count As Integer
Dim row_count As Integer
Dim col_count As Integer
Dim i As Integer
Dim j As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Mask FIT Matrix")
Set ws2 = ThisWorkbook.Sheets("Apr")

ws1.Activate
head_count = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlToRight)))

ws2.Activate
col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))


For i = 1 To head_count
   
    j = 1
   
    Do While j <= col_count
   
        If ws1.Cells(2, i) = ws2.Cells(1, j).Text Then
       
            ws2.Range(Cells(1, j), Cells(row_count, j)).Copy
            ws1.Cells(3, i).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            j = col_count
 
        End If
       
        j = j + 1

    Loop

Next i

With ws1
    .Activate
    .Cells(2, 1).Select
End With

End Sub

 

[Joe4]

You can use this line of code to clear the entire sheet:

ws1.UsedRange.ClearContents

If you have a header on row 1 you would like to leave, you could use this instead:

ws1.UsedRange.Offset(1, 0).ClearContents

 

[mattbird]

You can use this line of code to clear the entire sheet:

ws1.UsedRange.ClearContents

If you have a header on row 1 you would like to leave, you could use this instead:

ws1.UsedRange.Offset(1, 0).ClearContents

Thanks that works perfectly.

 

[Joe4]

You are welcome.
Glad I was able to help!