Remove and Replace all information

[nathandavies9]

Hi All,

I have a code which is assigned to a button, when the button is pressed the below macro runs and selects and copies all information which has a figure in column H and pastes it into a new work sheet "ORDER REC"

I was wondering if there is a way of deleting all the information in "ORDER REC" when the button is pressed and re-pasting the updated information into the work sheet.

Sub Order()

    Dim ws As Worksheet
    Dim lr As Long
    Dim last As Long
    Dim i As Long


Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "ORDER REC" And ws.Name <> "SUMMARY SHEET" And ws.Name <> "MANUFACTURING TIMES" And ws.Name <> "ELSTEEL" And ws.Name <> "COPPER" Then
            lr = ws.Range("C" & Rows.Count).End(xlUp).Row
            last = Sheets("ORDER REC").Range("C" & Rows.Count).End(xlUp).Row
            ws.Range("A2:L" & lr).Copy Sheets("ORDER REC").Range("A" & last + 1)
        End If
    Next ws


    With Sheets("ORDER REC")
    last = .Range("C" & Rows.Count).End(xlUp).Row
        For i = last To 2 Step -1
            If .Range("H" & i) = "" Then
            .Range("H" & i).EntireRow.Delete
            End If
        Next i
    End With


Application.ScreenUpdating = True
Application.CutCopyMode = False


    Sheets("ORDER REC").Select '
    Range("k" & (Range("k" & Rows.Count).End(xlUp).Row) + 2) = "= Sum(" & Range("k2", Range("k" & Rows.Count).End(xlUp)).Address & ")"
    
        ActiveWorkbook.Worksheets("ORDER REC").SORT.SortFields.Clear
    ActiveWorkbook.Worksheets("ORDER REC").SORT.SortFields.Add Key:=ActiveCell. _
        Offset(0, 3).Range("A1:A23"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("ORDER REC").SORT.SortFields.Add Key:=ActiveCell. _
        Offset(0, 2).Range("A1:A23"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("ORDER REC").SORT
        .SetRange ActiveCell.Offset(-1, 0).Range("A1:L24")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
       
        End Sub
This is my current code any help would be greatly appreciated.

 

[Fluff]

Add this before your first loop

Sheets("ORDER REC").cells.clear

 

[nathandavies9]

Add this before your first loop

Sheets("ORDER REC").cells.clear

Will this clear my headers?

 

[Fluff]

Will this clear my headers?

Yup, it deletes all the information on the sheet.