CODEWITHSUNDEEP
vbaexcel
user1551203
user1551203

Reputation: 33

Excel macro advice

I have the following macro running on a workbook to copy data with a specific criteria from "Master" sheet to "Quarantined" sheet;

Dim LR As Long, LR2 As Long

Application.ScreenUpdating = False

With Sheets("Quarantined")
    LR2 = .Range("L" & Rows.Count).End(xlUp).Row
    If LR2 > 2 Then
        .Range("A3:I" & LR2).ClearContents
    End If
End With

With Sheets("Master")
    LR = .Cells(Rows.Count, 8).End(xlUp).Row
    LR2 = Sheets("Quarantined").Range("L" & Rows.Count).End(xlUp).Row
    With .Range("L2:L" & LR)
        .AutoFilter Field:=1, Criteria1:="QUARANTINED"
        .Offset(1).Resize(LR).EntireRow.Copy Sheets("Quarantined").Range("A" & LR2 + 1)
        .AutoFilter
    End With
End With

Application.ScreenUpdating = True

It works perfectly but if I update the master and run the macro again it pastes it under the original information on the quarantined sheet. How do I get it to overwrite the information that was already there instead of pasting underneath?

Here's hoping

Upvotes: 1

Views: 116

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

It is pasting under the original info because you are telling it to.

You are clearing your range from .Range("A3:I" & LR2).ClearContents but you are taking the next available row from Col L LR2 = Sheets("Quarantined").Range("L" & Rows.Count).End(xlUp).Row

Either Change your code to this

.Range("A3:L" & LR2).ClearContents

or take the last row based on Col A instead of Col L

Upvotes: 2

Related Questions