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!

 

[vcoolio]

Could you upload a sample of your whole work book please with all sheets listed. Use the same method as in your post #12 .

Cheerio,
vcoolio.

 

[Bandittheone]

Vcoolio,

Here you go:
https://1drv.ms/u/s!AizpA15wjAfHjcAsVKYicY84nCw3xw

Thanks!

 

[Aberdham]

<b2,true,false)"
<b2,true,false)"
Hello Bandit,

If I've understood your request, then the following code may help:-

Sub Test()

     Dim ws As Worksheet
     Dim lr As Long, lr1 As Long
     
Application.ScreenUpdating = False
     
Sheet1.UsedRange.Offset(1).ClearContents

For Each ws In Worksheets
     If ws.Name <> "Sheet1" Then
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
ws.Range("D2:D" & lr) = "=if(C2 
With ws.[A1].CurrentRegion
             .AutoFilter 4, True
             .Offset(1).EntireRow.Copy
             Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
             .AutoFilter
             .Columns(4).ClearContents
             End With
       End If
Next ws

lr1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Sheet1.Range("D2:D" & lr1) = "=SUM(B2-C2)"

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code will insert a formula in Column D of each sheet (except your main sheet - sheet1 in the code) to determine if the values in Column C are less than the values in Column B. The formula determines a TRUE or FALSE value for each row and the code will filter on TRUE then transfer the relevant rows of data from each sheet to the main sheet (Sheet1). The code then inserts a formula in Column D of the main sheet to determine the "missing" value.

Following is the link to a small sample I prepared for you so that you can see how the code works. Click on the "RUN" button to see it work.

http://ge.tt/1AsUEfp2

I hope that this helps.

Cheerio,
vcoolio.</b2,true,false)"


Hi Vcoolio,

I saw your code that gets to combine data to a summary sheet, does it also work to split the data from multiple summary e.g. 2 and transfer into the respective sheets based on 1 or 2 criteria?

regards,
M</b2,true,false)"

 

[vcoolio]

Hello Bandit,

I've found a couple of little things that were interfering with the code with merged cells being the main problem in the "Missing" sheet.

Anyway, following is the link to your workbook with the code implemented:-

http://ge.tt/43dwKhp2

You'll see that it works just fine now.

Just to start afresh, do the following in your workbook(but perhaps test it all first in another copy of your workbook):-

- In the "Missing" sheet, click on row2 (the number 2 out to the left) to high-light the whole row.
- Press Ctrl+Shift+down arrow. This will high-light the whole data set that you may have in the "Missing" sheet.
- Right click anywhere within the high-lighted area then select delete.
- Now enter the headings:-

Skid, Item, QTY, Fill, Notes, Missing

from A2:F2- Save the changes.

[TABLE="width: 1267"]
<tbody>[TR]
[TD="class: xl71, width: 107"][/TD]
[TD="class: xl73, width: 226"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1267"]
<tbody>[TR]
[TD="class: xl73, width: 226"][/TD]
[/TR]
</tbody>[/TABLE]
This will totally clear the sheet of anything that may have interfered with the code previously.

Although I should, I shan't try and paste the code here as I'm still experiencing problems with parts of the code being cut off by the code tags. You should be able to retrieve the code from the sample above. There are only a couple of very minor tweaks to it.

Cheerio,
vcoolio.

Ha. You can see more weird things happening above!!

 

[vcoolio]

Hello Aberdham,

No. It won't do the task correctly for you.

Please start your own thread explaining, clearly, exactly what you would like to achieve. Supply a sample of your work book showing your inputs and the expected results. You can supply a sample by uploading it to a free file sharing site such as Drop Box or ge.tt then posting the link to your file in your opening post. Please use dummy data.

Cheerio,
vcoolio.

 

[Aberdham]

Hello Aberdham,

No. It won't do the task correctly for you.

Please start your own thread explaining, clearly, exactly what you would like to achieve. Supply a sample of your work book showing your inputs and the expected results. You can supply a sample by uploading it to a free file sharing site such as Drop Box or ge.tt then posting the link to your file in your opening post. Please use dummy data.

Cheerio,
vcoolio.

Thanks. I will sort out the data first and then quote you.

 

[Bandittheone]

Vcoolio,

Thanks a lot!

Now the only issue I immediately notice is it isn't copying over the titles of some of the boxes missing items, for example, box num 9's name is not copied over....I wonder why.

Thanks!

 

[vcoolio]

Hello Bandit,

I've found some very minor discrepancies in the set out between some sheets so, using the code, I think its all sorted now.
There are now a few more iterations for the code to go through so instead of taking 3 - 4 seconds to execute it may take 6 - 8 seconds to execute.

Anyway, here's a new link to the file. I still can't post the code here but you can retrieve it from the module in the sample.

http://ge.tt/5Stwdhp2

Cheerio,
vcoolio.

 

[Bandittheone]

vcoolio,

Works perfectly! Thanks again for your time.

My next task is looking to conditionally hide sheets(boxes) based on the "checklist" sheet's filtering on row 111. The checklist shrinks based on seasons, location, etc. How would you recommend I have the appropriate boxes(sheets) hide when the checklist hides them?

 

[vcoolio]

Hello Bandit,

Excellent! I'm glad that's sorted!

As for hiding sheets, it could be a bit problematic trying to hide them from a filtered list so, to keep it simple, I recommend:-

- Create a data validation list of all the sheet names in an empty cell nearby the filtered list.
- Once you have filtered for a particular sheet, use the following code assigned to a button to hide said sheet:-

Sub HideShts()

Dim shtNm As String

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

End Sub

Cheerio,
vcoolio.

P.S. Amazing! The tags worked for that little code!