Thread: If Cell = X, Then Copy Entire Row Into A New Sheet

Hi - as per title, could someone advise me how to do this please? Basically, I have a report that I use everyweek and split up into various sheets according to the criteria.

So if any cell in column C contains "Apple", then I want it copied into a new s/s (with the headers). I basically have to do a number of iterations on this, can someone assist please? The main problem I invisage is asking Excel to copy the row into the new sheet underneath the last one.

Many thanks, Jimmy.

Right, that's what I'm doing now. But I'm trying to get the vba coding in, to avoid the manual work.

Conditional Behaviour. So firstly each sheet will carry the same set of headers.

Then, if any cell in Column B contains "Apple", then the entire row needs to be copied into Sheet2 (one underneath each other after the header). If any cell in Column B contains "Orange", then the entire row needs to be copied into Sheet3, etc etc. I have a series of these I need to do.

Thanks for any help.

Here you go, so basically Sheet 1 is a mock-up of my extracted data. And the resulting three tabs are what I'd like generated from the macro (the Sheets are always pre-defined - or at least will be when I write the vba code).

Here is an invaluable piece of code I frequently use. I take no credit for its existence.

It doesn't do your job quite how you describe. Though it will split a database into sheets by column A. Either copy your column B to A, or change the code. So where it says A1, change to B1.

I'm sure you'll get a perfect solution shortly; knowledge is abundant around here.

Sub PagesByDescription()

Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String

    Set wSheetStart = ActiveSheet
    wSheetStart.AutoFilterMode = False
    
    'Set a range variable to the correct item column
    Set rRange = Range("A1", Range("A65536").End(xlUp))
        
        'Delete any sheet called "UniqueList"
        'Turn off run time errors & delete alert

        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("UniqueList").Delete
        'Add a sheet called "UniqueList"

        Worksheets.Add().Name = "UniqueList"
           
           'Filter the Set range so only a unique list is created

            With Worksheets("UniqueList")
                rRange.AdvancedFilter xlFilterCopy, , _
                 Worksheets("UniqueList").Range("A1"), True

                 'Set a range variable to the unique list, less the heading.

                 Set rRange = .Range("A2", .Range("A65536").End(xlUp))
            End With

            
            On Error Resume Next

            With wSheetStart
                For Each rCell In rRange
                  strText = rCell
                 .Range("A1").AutoFilter 1, strText
                    Worksheets(strText).Delete
                    'Add a sheet named as content of rCell
                    Worksheets.Add().Name = strText
                    'Copy the visible filtered range _
                    'default of Copy Method) and leave hidden rows
                    .UsedRange.Copy Destination:=ActiveSheet.Range("A1")
                    ActiveSheet.Cells.Columns.AutoFit
                Next rCell
            End With

        With wSheetStart
            .AutoFilterMode = False
            .Activate
        End With
  
        On Error GoTo 0

        Application.DisplayAlerts = True

End Sub

Hi - thanks for the help. The reason I wrote them as "MyOranges" is because the sheet name isn't going to be whatever the criteria of the cell is. They will take on pre-defined names which I can write into the VBA code pretty easily.

Also, am I going mad, or is there no code attached to the file lol?

Hi,
I am trying to do something very similar. If you look at the attached spreadsheet you will see 6 tabs. Main, New Cust, Quote, etc.

"Main" will be the sheet that everything is inputted. If there is a "Y" under new customer, I want the entire row to copy to the tab "New Cust". If there is an "X" under quote, struct design, or graphic design I want it under its approp. tab. The Lisa tab should take certain Sales-Person and copy entire row on the Lisa tab. The certain Sales-Persons are BS, PK, PB, PD.

Any help will be greatly appreciated.