Help with find and delete please

[EagerToLearn]

Hello there,
I have a simple question
I have a work book contains our sales for 5 years
Every sheet in this work book contains sales for one month.
Sales is by costumer in each sheet.
How can I create a simple macro that finds sales for a specific customer in every sheet, after finding the sales for that customer, either delete all other customers and leave that customer on the sheet, or make a copy of that sales and copy it to another file
The purpose of this macro is to get the sales for that customer for every month for the last 5 years.
I do not have a problem of having a macro works for one sheet and I can run it again for every single other sheet.
Your help is much appreciated.

 

[kpark91]

Does this sound like you need to buy a product? lol..

 

[John Davis]

Hello there,
I have a simple question
I have a work book contains our sales for 5 years
Every sheet in this work book contains sales for one month.
Sales is by costumer in each sheet.
How can I create a simple macro that finds sales for a specific customer in every sheet, after finding the sales for that customer, either delete all other customers and leave that customer on the sheet, or make a copy of that sales and copy it to another file
The purpose of this macro is to get the sales for that customer for every month for the last 5 years.
I do not have a problem of having a macro works for one sheet and I can run it again for every single other sheet.
Your help is much appreciated.

Untested, and not sure if something like this is what you're looking for, but maybe it'll give you some ideas.

Sub EagerToLearn()
Dim ws As Worksheet
Dim lr As Long

x = InputBox("Please Enter Customer Name to Search")

Sheets.Add.Name = x

For Each ws In ThisWorkbook.Sheets

    If ws.Name <> x Then
    
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    
        With Range(Range("A2"), Range("A" & lr))
        
            .AutoFilter Field:=1, Criteria1:=x
            .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(x).Range("A" & Rows.Count).End(xlUp)(2)
            .AutoFilter
            
        End With
        
    End If
    
Next
        
End Sub

 

[EagerToLearn]

Hello,
Thank you very much for your help, I will go ahead and try it.
really appreciate the time you spent on that.

 

[John Davis]

Hello,
Thank you very much for your help, I will go ahead and try it.
really appreciate the time you spent on that.

Be sure to try it on a copy. It is untested.

 

[EagerToLearn]

Hello
I tried it.
I was using step by step (F8) to see where it is giving the error message number 400.
It creates the x sheet and goes through the if statement but returns no data, then the second time it goes through the if statement and gets stuck in this line
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(x).Range("A" & Rows.Count).End(xlUp)(2).
Is there any way I can sent you a test file with some date in it?
Thanks

 

[John Davis]

Hello
I tried it.
I was using step by step (F8) to see where it is giving the error message number 400.
It creates the x sheet and goes through the if statement but returns no data, then the second time it goes through the if statement and gets stuck in this line
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(x).Range("A" & Rows.Count).End(xlUp)(2).
Is there any way I can sent you a test file with some date in it?
Thanks

See my PM.

 

[John Davis]

Hello
I tried it.
I was using step by step (F8) to see where it is giving the error message number 400.
It creates the x sheet and goes through the if statement but returns no data, then the second time it goes through the if statement and gets stuck in this line
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(x).Range("A" & Rows.Count).End(xlUp)(2).
Is there any way I can sent you a test file with some date in it?
Thanks

Maybe with these changes:

Sub EagerToLearn()
Dim ws As Worksheet
Dim lr As Long

x = InputBox("Please Enter Customer Name to Search")

Sheets.Add.Name = x

For Each ws In ThisWorkbook.Sheets

    If ws.Name <> "x" Then
    
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    
        With Range(Range("A2"), Range("A" & lr))
        
            .AutoFilter Field:=1, Criteria1:=x
            .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(x).Range("A" & Rows.Count).End(xlUp)(2)
            .AutoFilter
            
        End With
        
    End If
    
Next
        
End Sub

 

[EagerToLearn]

Hello John,
Just sent you the excel file via e-mail,
Your help is greatly appreciated

 

[John Davis]

Hello John,
Just sent you the excel file via e-mail,
Your help is greatly appreciated

See the email, I just sent you. with revised code.