import out of stock item to another sheet

[rapitorres]

Hi.

what i have here is a little inventory system and its working with barcode.
in the OUT sheet all out of stock is automatically marked red. im thinking is there a way that i can import all (0) or out of stock products
to a different sheet and automatically remove when Stocked, so that i can monitor all the OUT OF STOCK ITEMS

feel free to navigate to my simple inventory
btw due to large amount of data manual calculation is applied. please press F9 for calculation

030821.xlsm

drive.google.com

 

[My Aswer Is This]

Seeing that no one has helped you with this question. I suggest you give more specific details. I never download and open files posted here on this forum.

 

[rapitorres]

Seeing that no one has helped you with this question. I suggest you give more specific details. I never download and open files posted here on this forum.

Thankyou for replying

here ive put some pointers.

 

[My Aswer Is This]

So on sheet named "Out" when the value in column G is manually changed to O that's a zero
You want this row of Data copied to a sheet named "Out Of Stock" which is already created.
Is that true?
And if that zero in column G of sheet named "Out" is changed back to any thing greater then zero you want that row deleted from sheet named "Out Of Stock"

Is this all true?
And these values are being changed manually and not the result of a formula. Is this true?

And is there some sort of unique value in some column which could be used as a identifier of the row?
Sort of like a customer id number or unique part number?

Like can we use column A which is a barcode number. Would that work?

So if on sheet Out the number in column G is changed to zero we copy that row to sheet named out of stock.
But if later column G is changed to a value greater then zero the script looks for the barcode number in column A and deletes that row in sheet named Out of stock.

If this something that would work?
If not give me more information.

 

[My Aswer Is This]

There are two ways to run a script:
You can press a button for example.
Or you can manually change a cell value.
Like change 5 to 0 would activate a script or just enter a zero.
Which are you more interested in?

 

[rapitorres]

So on sheet named "Out" when the value in column G is manually changed to O that's a zero
You want this row of Data copied to a sheet named "Out Of Stock" which is already created.
Is that true?
And if that zero in column G of sheet named "Out" is changed back to any thing greater then zero you want that row deleted from sheet named "Out Of Stock"

Is this all true?
And these values are being changed manually and not the result of a formula. Is this true?

And is there some sort of unique value in some column which could be used as a identifier of the row?
Sort of like a customer id number or unique part number?

Like can we use column A which is a barcode number. Would that work?

So if on sheet Out the number in column G is changed to zero we copy that row to sheet named out of stock.
But if later column G is changed to a value greater then zero the script looks for the barcode number in column A and deletes that row in sheet named Out of stock.

If this something that would work?
If not give me more information.

So on sheet named "Out" when the value in column G is manually changed to O that's a zero
You want this row of Data copied to a sheet named "Out Of Stock" which is already created.
Is that true?YEP ANY ZERO (0) THAT IS LISTED IN G COLUMN WILL BE AUTOMATICALLY COPIED OR MOVE TO THE SHEET (OUT OF STOCK SHEET) BTW THE SHEET WAS CREATED FOR SAMPLE PURPOSE ONLY

And if that zero in column G of sheet named "Out" is changed back to any thing greater then zero you want that row deleted from sheet named "Out Of Stock"

YEP THIS IS CORRECT

Is this all true?
And these values are being changed manually and not the result of a formula. Is this true?

NOPE ALL OF THIS IS RUN BY FORMULA. (SUMIFS AND VLOOKUP) THE ONLY MANUALLY ENTERED IS THE (E) COLUMN WHICH IS THE QTY THAT DEFINES HOW MANY STOCKS WILL BE SOLD OR OUT

And is there some sort of unique value in some column which could be used as a identifier of the row?
Sort of like a customer id number or unique part number?

IM CURRENTLY USING THE BARCODE IN COLUMN (A) AS AN IDENTIFIER

Like can we use column A which is a barcode number. Would that work?

YEP!

So if on sheet Out the number in column G is changed to zero we copy that row to sheet named out of stock.
But if later column G is changed to a value greater then zero the script looks for the barcode number in column A and deletes that row in sheet named Out of stock.

EXACTLY WHAT I WANTED

If this something that would work?
If not give me more information.

 

[rapitorres]

There are two ways to run a script:
You can press a button for example.
Or you can manually change a cell value.
Like change 5 to 0 would activate a script or just enter a zero.
Which are you more interested in?

MAYBE ASLONG THAT THE VALUE IS ZERO IT WILL BE AUTOMATICALLY COPIED OR MOVE TO THE (OUT OF STOCK SHEET)

 

[My Aswer Is This]

Now that you say the values in column G are not being changed to zero manually then the script would have to run when you press a button which would not be automatically.

So can we just have the script look at sheet named Out and if it finds a zero in column G copy this row to sheet named Out of stock and not delete the row from sheet named Out.

Next time you run the script we clear the sheet named Out of stock and then copy all the rows on sheet named Out that has a zero in column G to sheet named Out of stock.

This would only happen when you click a button.
Would this work?

 

[rapitorres]

Now that you say the values in column G are not being changed to zero manually then the script would have to run when you press a button which would not be automatically.

So can we just have the script look at sheet named Out and if it finds a zero in column G copy this row to sheet named Out of stock and not delete the row from sheet named Out.

Next time you run the script we clear the sheet named Out of stock and then copy all the rows on sheet named Out that has a zero in column G to sheet named Out of stock.

This would only happen when you click a button.
Would this work?

YEAH! THATS PERFECT

 

[My Aswer Is This]

This script does this:
1. Deletes all rows on sheet named Out Of stock below row(1) I assume row(1) is a header row.
Let me know if you want that modified or modify yourself see note in script.
2. Any row on sheet named "Out" with a zero in column G will be copied to sheet name "Out Of Stock"

Sub Filter_Me_Please()
'Modified  3/12/2021  5:04:53 AM  EDT
Application.ScreenUpdating = False
Dim lastrow As Long
Dim lastrowa As Long
Dim c As Long
Dim s As Variant
c = 7
s = "0"
lastrow = Sheets("Out").Cells(Rows.Count, c).End(xlUp).Row
lastrowa = Sheets("Out Of Stock").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Out Of Stock").Rows(2).Resize(lastrowa).Delete 'modify 2 to 3 or 4 what ever you need

With Sheets("Out").Cells(1, c).Resize(lastrow)

    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Out Of Stock").Cells(2, 1)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub