copying contents from multiple sheets and paste to one sheet while clearing contents before.

[whitebutterfly000]

Hello all,
Hope you can answer me I have been trying to add
(clearcontents) to the code below that I have to copy from all sheets , clearcontents in master sheet before paste then paste , and everytime I am getting an error.

to sum up what Im trying to do, copy contents from all sheets I have in this workbook and paste in in the ARCHIVE sheet, and everytime it pastes I want it to clear contents on the ARCHIVE sheet then paste so that it updates in stead of duplicating value.

Sub CopyToMaster()
 
ShtCount = ActiveWorkbook.Sheets.Count
 
For I = 2 To ShtCount
 
Worksheets(I).Activate
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
 
Range("a2:N" & LastRow).Select
 
Selection.Copy
Sheets("ARCHIVE").Activate
 
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select
 
'Required after first paste to shift active cell down one
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
 
ActiveCell.Offset(0, 0).Select
Selection.PasteSpecial
ActiveWorkbook.Save

 
Next I
End Sub

Sub M()

Application.OnTime Now + TimeValue("00:00:10"), "CopyToMaster"


End Sub

Im gonna post my code here if anyone can tell me where to put it please.

 

[kevin9999]

Option Explicit
Sub whitebutterfly()
    Dim ws As Worksheet, LRow As Long, LCol As Long, i As Long
    Set ws = Worksheets("ARCHIVE")
    LRow = WorksheetFunction.Max(ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row, 2)
    LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    
    'Next line clears the ARCHIVE sheet
    ws.Range(ws.Cells(2, 1), ws.Cells(LRow, LCol)).ClearContents
    
    For i = 2 To Sheets.Count
        LRow = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
        Sheets(i).Range("A2:N" & LRow).Copy _
        ws.Range("A" & ws.Cells(Rows.Count, 1).End(xlUp).Row + 1)
    Next i
End Sub