VB SCRIPT HELP - Delete Data from an excel spreadsheet based on certain cell value

[laurensims41]

I Have to pull a report every morning that is very lengthy and has everyone in the organization's data on it. I would like to be able to delete everyone else's data that are not the 10 people that report up to me


The Spreadsheet goes from Cells A to AK

COLUMN AM has the data of the people I would like to keep the information on. Any Help would be appreciated.
Lauren Sims
Jocy Sims
Kendal Perkins
Breanne Lewis
James Sims
Laura Bedwell
Betsy Bedwell
Ryan Enochs
Chris Bowne
Shannon Erlenbush
Danielle Futrell

 

[NoSparks]

Assuming row 1 A-AK is headers and AM1 has the exact same header as the column you look in for the names
and your people are in AM2 and down


You can extract your people to another sheet using Advanced Filter
Something along the lines of this

Sub ExtractMyPeople()


Dim src As Worksheet, dest As Worksheet
Dim lr As Long, critRng As Range


Set src = Sheets("Sheet1")
Set dest = Sheets("Sheet2")


With src
    lr = .Columns("A:AK").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set critRng = .Range("AM1", .Cells(Rows.Count, "AM").End(xlUp))
    .Range("A1:AK" & lr).AdvancedFilter xlFilterCopy, critRng, dest.Cells(1)
End With


End Sub

 

[NoSparks]

Did that work for you ?

 

[laurensims41]

It didn't pull any data passed Column AM, but it did put data into another sheet. I really appreciate your help on this.

Your above Assumptions are correct.

The Column AM is titled Buyer

I am not seeing where I would designate the names of the individuals I am trying to pull out of the report?

 

[NoSparks]

It didn't pull any data passed Column AM

Should it ?
From post 1

The Spreadsheet goes from Cells A to AK

made me wonder about column AM
I can't see your spreadsheet, can only guess at what you're working with from how you describe it.
Perhaps you want to elaborate a little more ?

 

[laurensims41]

I am sorry I really mis-typed the original ask.

The Spreadsheet is from A to AZ.
Header's in ROW 1.



I would like all data associated with the following names moved to a separate sheet or everyone deleted: These Names are in Column AN w/ a Header BUYER;
Lauren Sims
Jocy Sims
Kendal Perkins
Breanne Lewis
James Sims
Laura Bedwell
Betsy Bedwell
Ryan Enochs
Chris Bowne
Shannon Erlenbush
Danielle Futrell

 

[Fluff]

Do you want to keep those names, or delete them?

 

[NoSparks]

How about this ?
You add a new sheet and name it People
In A1 put the same header as your AN (Buyer) column has.
In A2 and down, list the people whose data you want to extract to another sheet.
This range on the People sheet, (A1:A12) according to the list of 11 names you've supplied, will be used as the criteria range to advance filter the src sheet.

Sub ExtractMyPeople()

    Dim src As Worksheet, dest As Worksheet
    Dim lr As Long, critRng As Range

Set src = Sheets("Sheet1")      'change sheet name if necessary
Set dest = Sheets("Sheet2")     'change sheet name if necessary
Set critRng = Sheets("People").Range("A1", Sheets("People").Range("A" & Rows.Count).End(xlUp))

With src
    lr = .Columns("A:AZ").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    .Range("A1:AZ" & lr).AdvancedFilter xlFilterCopy, critRng, dest.Cells(1)
End With

End Sub