Delete rows in three tabs based on value and save file

[mdgabe]

I have about 450 values that is in column A of Sheet1, Sheet2 and Sheet3. Can someone give me guidance on code needed to cycle through those 450 values and only keep the same value in that column in all three tabs and save as new file then move to do the same for the next value.
Example:
Sheet1
HDR1 HDR2
70101 parta
70102 partb
70104 partf
70809 partg

Sheet2
HDR1 HDR2
70101 custa
70102 custb
70104 custf
70809 custg

Sheet3
HDR1 HDR2
70101 custaddra
70102 custaddrb
70104 custaddrf
70809 custaddrg

I need the files that are saved to have Sheet1, Sheet2 and Sheet3 but only for say 70101 with 70101 rows only in all three tabs and saved as 70101.xls. Is this even possible?
Thanks in advance

 

[Gavona]

Do you have a definitive list of all the different HDR1 values or is creating one one of the tasks? Does one of the sheets contain all possible values?

I see the process as:
1. Open the Source workbook
2. Obtain list of unique HDR1 value in a range on a sheet in the workbook containing your code
3. Step through the list (For each MyCell in MyList...)
a)applying autofilters to each sheet and deleting the rows not containing MyCell.value.
b) Save the workbok and close it.
3. Open the Source Workbbook, goto next item in the list

One can obtain a list of unique values using advanced filter.

Make a copy of the workbook. Work on the copy.

You don't want the code you develop to be stored in this workbook so open a new one, Switch on macro recorder (Tools, Macro, record new macro....).

Apply autofilter to sheet1 and use custom filter to filter for not equal to the first value. Delete the visible rows. Remove autofilter. Save with a new name. Switch off macro recorder.

Examine the code and try to modify it so that it will do the filtering for all three sheets.

Now look to modify the macro to loop through the list of possible values in column A repeating the above actions.

Post back with whatever code you have developed and the issues it is giving you (or just to get folk to advise you how to improve it).


Gavin

 

[mdgabe]

Thanks for the reply, I got this piece of code from Ron De Bruin site, I think something like this is what I need, I just not sure how to modify it. I could list my 450 values on the sheet and proceed from there. Each of the three sheet will have all 450 values, is it a problem if one of the sheets does not?

Sub Delete_with_Autofilter_More_Criteria()
    Dim rng As Range
    Dim cell As Range
    Dim Criteriarng As Range
    Dim CalcMode As Long
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    With Sheets("Criteria")
        Set Criteriarng = .Range("A1", .Cells(Rows.Count, _"A").End(xlUp))
    End With
    For Each cell In Criteriarng
        With Sheets("Sheet1")
            .Range("A1", .Cells(Rows.Count, "A").End(xlUp))         
_           .AutoFilter Field:=1,Criteria1:=cell.Value
            With .AutoFilter.Range
                Set rng = Nothing
                On Error Resume Next
                Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) 
_               .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rng Is Nothing Then rng.EntireRow.Delete
            End With
            .AutoFilterMode = False
        End With
    Next cell
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

 

[Gavona]

Each of the three sheet will have all 450 values, is it a problem if one of the sheets does not?

I asked originally firstly in case we needed to extract a complete list and secondly because if the autofilter produces no result that can need handling in the code. I suspect that the error trapping in the code you have picked up deals with the situation.

I would highly reccomend that you try it (on test data) and (i) check that it works as expected and (ii) see if you can break it.
Consider, for example, that if your Criteria sheet omits a possible value (as new values are introduced in the future) then that will not get picked up.


Gavin