Macro to extract Data

[howard]

I have written code to extract data which works perfectly

 Sub Extract_Data()
Dim LR As Long
Sheets("Sales").Select
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:O" & LR).ClearContents
Range("A1:O" & LR).ClearFormats
Sheets("Imported Data").Columns("A:O").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("'Sales'!Criteria"), CopyToRange:=Range( _
        "A1"), Unique:=False
End Sub

However, if I amend code to say With Sheets ("Sales") and code is amended as follows and I run the macro from another sheet eg "Macro, the data is extracted to sheet "Macro

 Sub Extract_Data()
Dim LR As Long
With Sheets("Sales")
LR = Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:O" & LR).ClearContents
.Range("A1:O" & LR).ClearFormats
Sheets("Imported Data").Columns("A:O").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("'Sales'!Criteria"), CopyToRange:=Range( _
        "A1"), Unique:=False
End With
End Sub

It would be appreciated if someone could amend my code





End Sub

 

[MARK858]

Sub Extract_Data()
    Dim LR As Long
    With Sheets("Sales")
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A1:O" & LR).ClearContents
        .Range("A1:O" & LR).ClearFormats
        Sheets("Imported Data").Columns("A:O").AdvancedFilter Action:=xlFilterCopy, _
                                                              CriteriaRange:=Range("'Sales'!Criteria"), CopyToRange:=.Range( _
                                                                                                                      "A1"), Unique:=False
    End With
End Sub

 

[MARK858]

Please see edit in previous post as you had another issue as well.

 

[howard]

Thanks for the help, Mark

 

[MARK858]

You're welcome