Export rows to another sheet if the quantity for a device is not zero

[theredjay]

Hello
I have attached a workbook where I would like to export from the "info" sheet only the rows within the range C2:F60 if the Quantity (column E) for a device is not zero; these rows should be exported to a new sheet within the "example" workbook. Something to note is that the information for the Device Name (Column C) and the Number (Column D) comes from the "data" sheet through a simple formula, so I think the data needs to be exported as values. Thank you

 

[theredjay]

Okay, slightly different approach then. Instead of copying each row at a time and only moving the rows where E>0, we can copy the entire range C2:F60 to the new sheet (to preserve the formatting) then loop through the data and delete the rows where E=0. Currently, it only pastes the values of the formulas, so if you want the formulas to copy over to the new sheet as well we'll have to adjust it a little.

Private Sub MoveData()
Dim wsA, wsB As Worksheet:      Set wsA = Sheets("info"):     Set wsB = Sheets("New Sheet") 'Change "New Sheet" to the name of the target sheet where data is moved to.
Dim i, lRow&:                   'lRow = wsB.Range("A" & Rows.Count).End(xlUp).Row + 1        'Identify the last used row on target sheet and add 1 to get the next empty row.

wsA.Range("C2:F60").Copy
With wsB.Range("C2:F60")
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
End With

'Loop through the range C2:F60 and delete rows where col E = 0.
For i = 60 To 9 Step -1
        If wsB.Range("E" & i).Value = 0 And wsB.Range("E" & i).Value <> "QUANTITY" And wsB.Range("E" & i) <> "" Then
                wsB.Range("C" & i & ":F" & i).Delete Shift:=xlUp
        End If
Next i
End Sub

Its perfect! it does exactly want I need, but something I noticed was if I add more equipment rows, and if many of the equipment rows have zero quantity that the output looks cramped with unnecessary data, would it be possible to remove for example rows 31-51 from the uploaded image?
thank you

 

[dreid1011]

Its perfect! it does exactly want I need, but something I noticed was if I add more equipment rows, and if many of the equipment rows have zero quantity that the output looks cramped with unnecessary data, would it be possible to remove for example rows 31-51 from the uploaded image?
thank you

Yeah, I'll take a look at it later and adjust the code.

 

[dreid1011]

Here is the updated code. Also, look at the attached image to see the range it will delete. Make sure you are consistent when adding equipment sections to keep the formatting the same or it might delete rows you want to keep. Deletes from Total row to 1 row above Equipment name for empty sections.

Private Sub MoveData()
Dim wsA, wsB As Worksheet:      Set wsA = Sheets("info"):     Set wsB = Sheets("New Sheet") 'Change "New Sheet" to the name of the target sheet where data is moved to.
Dim i&, j&, lRow&:              lRow = wsA.Range("C" & Rows.Count).End(xlUp).Row           'Identify the last used row on info sheet.

wsA.Range("C2:F" & lRow).Copy
With wsB.Range("C2:F" & lRow)
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
End With

'Loop through the range C2:F60 and delete rows where col E = 0 and other entire equipment sections if no devices present.
For i = lRow To 9 Step -1
    With wsB
        If .Range("D" & i).Value = "Total" And .Range("F" & i).Value = 0 Then
            For j = i To 8 Step -1
                If InStr(1, .Range("C" & j).Value, "Equipment") > 0 Then
                    .Range("C" & i & ":F" & j - 1).Delete Shift:=xlUp
                    Exit For
                End If
            Next j
        End If
        If .Range("E" & i).Value = 0 And .Range("E" & i).Value <> "QUANTITY" And .Range("E" & i) <> "" Then
            .Range("C" & i & ":F" & i).Delete Shift:=xlUp
        End If
    End With
Next i
End Sub