Multiple sheet inventory management: Conditionally copy missing items to another sheet?

[Bandittheone]

Hello,

We currently use a Excel doc with about 50 sheets, each representing a different box that is shipped/loaned frequently and needs to be rechecked often to confirm it has it's full inventory. Line items(rows) are also often added/removed to each box. The desired amount of item X is always in the same column and the current "Fill" is directly beside it.

I'm looking for one sheet to display rows with only missing items from all 50 boxes.

For example:

Sheet 1
Box 1 Desired Qty Actual Qty
Item A 10 5
Item B 5 5
Item C 20 10

Sheet 2
Box 2 Desired Qty Actual Qty
Item A 15 5
Item G 50 46
Item H 200 200




Sheet 3
Desired Qty Actual Qty Missing Qty
Item A 25 10 15
Item C 20 10 10
Item G 50 46 4


The items in the boxes change frequently, with new ones being added monthly, so creating a database of all items is not the best solution.

Thanks!

 

[Bandittheone]

vcoolio,

So basically:

1. In the "Checklist" sheet(the sheet with the list that gets filtered) Create a column where each cell is the name of the sheets that could potentially be filtered/hidden. These names would be on identical rows as the actual hidden rows, so when the filtering happens, the sheet name cells are also hidden.

2. Upon pressing the button, the code would look for the visibility of the list above and hide the corresponding sheet.

In your code, the "J101" should be the cell above that is hidden with filtering?

And the "sheet4" should be the sheet that should be hidden? Should it be the actual name of the sheet?

Thanks!

 

[vcoolio]

Hello Bandit,

1) I meant create a data validation list in a cell. To do this, select the cell that you want the list in then:-
- Click on the Data tab in the ribbon then select Data Validation from the Data Tools group.
- From the drop down, select Data Validation. The Data Validation dialogue box will appear.
- In the "Allow" box, select "List".
- In the "Source" box that then appears, type in the names of all your sheets. Separate each name with a comma. Do not use spaces at all. Do not place a comma after the last entry. Make sure that you spell/punctuate each sheet name exactly as per the tabs.
- Click OK.
You'll notice a drop down arrow will appear beside the selected cell. Click on it to see all your sheets listed.

2) On clicking on a button, the code will hide the sheet that you select from the drop down in the cell.

3) Cell J101 was just a random selection for the sake of placing it in the code as an example.

4) Sheet4 is the sheet code for the "Checklist" sheet. If you go to the VB Editor, in the Project Explorer over to the left, you'll see all your sheets listed with their names in parentheses and the sheet codes immediately to the left of the sheet names. My preference in coding is to use the sheet code.

Once you've done your filtering/analysis, select the sheet name from the drop down in the drop down cell and hide the sheet. It may be a good idea not to place "Checklist" in the data validation list.

Rather than use a button for this purpose, place the following code in the "Checklist" sheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

Dim shtNm As String

If Intersect(Target, Range("J101")) Is Nothing Then Exit Sub '---->Change cell reference to suit.

shtNm = Sheet4.[J101].Value  '---->Change cell reference to suit.
Sheets(shtNm).Visible = False

End Sub

Now, every time that you select a sheet name from the drop down selection and click away, the relevant sheet will be hidden. Remember to change the cell reference to suit yourself.

To implement the code:-

- Right click on the "Checklist" sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.

 

[Bandittheone]

Thanks a lot vcoolio. I really appreciate your help!

 

[vcoolio]

You're welcome Bandit. Glad that I was able to help.

Cheerio,
vcoolio.