Copy a row into a new sheet based on criteria

[ClaireGoode]

Hi,

I have a spreadsheet with vast amounts of data. One of the columns will have either 0.00% or a higher positive % value.

Where the row contains more than 0.00% in this column I would like the row to be copied into another sheet so that I can do some more formulas on this data. It needs to be in a separate sheet and copied not cut (i.e. must stay in the original sheet too).

All of the searches I've done so far on the web point to VBA but I have zero knowledge of VBA/script writing and I wondered if anyone has any other ways to do this?

Many thanks in advance for any help/suggestions.

(PS, looking for a solution in Excel and Google Sheets if possible!)

Claire.

 

[JackDanIce]

Hi Claire,

Have you tried to apply a filter to the data for any value in column I that is non-negative (i.e. > = 0) and the copied the filtered results? This avoids using VBA and seems straight forward to do for your ask.

Alternatively, you could copy your worksheet to a new sheet, sort the data by column I (say high to low) then delete all the rows (near the bottom) that are negative.

 

[ClaireGoode]

Thank you.

This is the solution I'm already working with, but the spreadsheet in question is shared by several team members with varying levels of Excel skill and I was hoping that when they enter data into sheet1 it would just copy over to sheet2 and I can then work only on the data in sheet2 rather than having to do the filtering, coy pasting, etc. (The main data in sheet1 has circa 50k rows and circa 50 columns and the number of rows is only going to increase over time!)

Maybe I need to get a VBA techie on board!

Thanks again!

 

[JackDanIce]

What is:

- The name of the sheet (Sheet1?) that contains the data
- The last used column on this sheet
- The row the data starts on, on this sheet
- Is this row a header row and does the data start in the row below?

- The name of the sheet (Sheet2?) that the data should be copied to
- The first row to copy the data to

 

[ClaireGoode]

- The name of the sheet (Sheet1?) that contains the data 302210-INITIAL-ITEMS
- The last used column on this sheet BF
- The row the data starts on, on this sheet Row 3
- Is this row a header row and does the data start in the row below? No, rows 1 & 2 are headers

- The name of the sheet (Sheet2?) that the data should be copied to COI-ITEMS
- The first row to copy the data to Ideally the first available/blank row (as we will continue to add to this); if not row 3

 

[JackDanIce]

Try this on a copy of your workbook:

Sub MoveNonNeg()

    Dim x   As Long
    Dim y   As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("302210-INITIAL-ITEMS")
        
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.count, 9).End(xlUp).row
        y = .Cells(3, .Columns.count).End(xlToLeft).column
        
        y = Application.Max(y, Range("BF1").column)
        
        With .Cells(3, 1).Resize(x - 2, y)
            .AutoFilter Field:=9, Criteria1:=">=" & 0
            .Offset(1).Resize(x - 3).SpecialCells(xlCellTypeVisible).Copy
        End With
    End With
    
    With Sheets("COI-ITEMS")
        'This line clears all cells in COI-ITEMS, this may required adjusting      
        .Cells.ClearContents
        .Cells(3, 1).PasteSpecial xlPasteValues
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

End Sub

Parts in blue relate to your answers
In green is a comment which will not execute
This link is search results when searching for: "How to add a macro to Excel": http://tinyurl.com/y7v4k6ud