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]

I forgot to mention on my previous post if someone could help me with the VBA code to complete the task

 

[theredjay]

could someone help me?

 

[dreid1011]

Assuming you have headers in row 1 of your example sheet and entered into column A, give this a try on a copy of your sheet:

What is the name of your second workbook where the "example" sheet is located?

Private Sub MoveData()
Dim wsA, wsB As Worksheet:      Set wsA = Sheets("info"):     Set wsB = Sheets("example")
Dim i, lRow&:                   lRow = wsB.Range("A" & Rows.Count).End(xlUp).Row + 1

For i = 2 To 60
    With wsA
        If .Range("E" & i).Value > 0 Then
            With wsB
                .Range("A" & lRow).Value = wsA.Range("C" & i).Value
                .Range("B" & lRow).Value = wsA.Range("D" & i).Value
                .Range("C" & lRow).Value = wsA.Range("E" & i).Value
                .Range("D" & lRow).Value = wsA.Range("F" & i).Value
            End With
        lRow = lRow + 1
        End If
    End With
Next i
End Sub

 

[theredjay]

Assuming you have headers in row 1 of your example sheet and entered into column A, give this a try on a copy of your sheet:

What is the name of your second workbook where the "example" sheet is located?

Private Sub MoveData()
Dim wsA, wsB As Worksheet:      Set wsA = Sheets("info"):     Set wsB = Sheets("example")
Dim i, lRow&:                   lRow = wsB.Range("A" & Rows.Count).End(xlUp).Row + 1

For i = 2 To 60
    With wsA
        If .Range("E" & i).Value > 0 Then
            With wsB
                .Range("A" & lRow).Value = wsA.Range("C" & i).Value
                .Range("B" & lRow).Value = wsA.Range("D" & i).Value
                .Range("C" & lRow).Value = wsA.Range("E" & i).Value
                .Range("D" & lRow).Value = wsA.Range("F" & i).Value
            End With
        lRow = lRow + 1
        End If
    End With
Next i
End Sub

Hello

I tried the VBA code but it did not work. I uploaded the file so its easier to come up with the VBA code, file can be downloaded at

Gofile - Free Unlimited File Sharing and Storage

Gofile is a free, secure file sharing and storage platform. With unlimited bandwidth and storage, you can easily store and share files of any type without any limits. Our advanced features, such as CDN support and password protection, make Gofile the ideal choice for individuals and businesses...

gofile.io

Thank you

 

[dreid1011]

Hello

I tried the VBA code but it did not work. I uploaded the file so its easier to come up with the VBA code, file can be downloaded at

Gofile - Free Unlimited File Sharing and Storage

Gofile is a free, secure file sharing and storage platform. With unlimited bandwidth and storage, you can easily store and share files of any type without any limits. Our advanced features, such as CDN support and password protection, make Gofile the ideal choice for individuals and businesses...

gofile.io

Thank you

I can take a look at that in a few hours when I am home.

 

[dreid1011]

Okay, you haven't said what name the new sheet is called, nor have you created it yet, so I used a temp sheet called "New Sheet" to copy the data to. Once you create the new sheet and name it, change the sheet name in the code where I have indicated, and this code should work.

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.

'Loop through the range C2:F60 and check for col E values greater than 0 and exclude header rows.
For i = 2 To 60
    With wsA
        If .Range("E" & i).Value > 0 And .Range("E" & i).Value <> "QUANTITY" Then
        
            'Copy data from info sheet to target sheet.
            With wsB
                .Range("A" & lRow).Value = wsA.Range("C" & i).Value
                .Range("B" & lRow).Value = wsA.Range("D" & i).Value
                .Range("C" & lRow).Value = wsA.Range("E" & i).Value
                .Range("D" & lRow).Value = wsA.Range("F" & i).Value
            End With
        lRow = lRow + 1
        End If
    End With
Next i
End Sub

 

[dreid1011]

A few edits to the code to shorten it a bit.

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.

'Loop through the range C2:F60 and check for col E values greater than 0 and exclude header rows.
For i = 2 To 60
        If wsA.Range("E" & i).Value > 0 And wsA.Range("E" & i).Value <> "QUANTITY" Then
                'Copy data from info sheet to target sheet.
                wsB.Range("A" & lRow & ":D" & lRow).Value = wsA.Range("C" & i & ":F" & i).Value
                lRow = lRow + 1
        End If
Next i
End Sub

 

[theredjay]

A few edits to the code to shorten it a bit.

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.

'Loop through the range C2:F60 and check for col E values greater than 0 and exclude header rows.
For i = 2 To 60
        If wsA.Range("E" & i).Value > 0 And wsA.Range("E" & i).Value <> "QUANTITY" Then
                'Copy data from info sheet to target sheet.
                wsB.Range("A" & lRow & ":D" & lRow).Value = wsA.Range("C" & i & ":F" & i).Value
                lRow = lRow + 1
        End If
Next i
End Sub

Hello
Is it possible to make the "New Sheet" output look like my new uploaded file (uploaded picture as well) where I have deleted rows 14, 15, 18, 26, 29, 30, 37-42, and 50.

Gofile - Free Unlimited File Sharing and Storage

Gofile is a free, secure file sharing and storage platform. With unlimited bandwidth and storage, you can easily store and share files of any type without any limits. Our advanced features, such as CDN support and password protection, make Gofile the ideal choice for individuals and businesses...

gofile.io

thank you

 

[dreid1011]

Hello
Is it possible to make the "New Sheet" output look like my new uploaded file (uploaded picture as well) where I have deleted rows 14, 15, 18, 26, 29, 30, 37-42, and 50.

Gofile - Free Unlimited File Sharing and Storage

Gofile is a free, secure file sharing and storage platform. With unlimited bandwidth and storage, you can easily store and share files of any type without any limits. Our advanced features, such as CDN support and password protection, make Gofile the ideal choice for individuals and businesses...

gofile.io

thank you

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