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]

Hi Bandit,

The link won't allow me to open it. Try it from your personal PC but use the ge.tt free file sharing site.

Here's the unfinished line from post#5:-

ws.Range("D2:D" & lr) = "=if(C2<b2,true,false)"
<b2,true,false)"


Aarrgghh!!. The site still won't allow me to post the whole formula!

I'll have to wait for a Moderator to have a look.

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

 

[Bandittheone]

Hello,

I have placed my file into a zip file so now it will automatically download instead of trying to open in excel online. Let me know if it works.

https://1drv.ms/u/s!AizpA15wjAfHjcAbzJ16oxtXMXMKeA

Could you try posting that code into a word file and uploading it maybe?

Thanks!

 

[Bandittheone]

Would it be possible to copy the whole line, including the "notes" column D, column A and all formatting to the "missing sheet"?

I'm also looking at having the row that is bold below copied before the rows of missing items. Below is an example of the box formatting I use.


[TABLE="class: grid, width: 1012"]
<tbody>[TR]
[TD]ARCG[/TD]
[TD]Type: Skip Box[/TD]
[TD="colspan: 2"]Size: 40x48x36[/TD]
[TD]Weight:[/TD]
[/TR]
[TR]
[TD]Skid[/TD]
[TD]Item[/TD]
[TD]Qty[/TD]
[TD]Fill[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]To Be Issued Skip Box All Season[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Goggles[/TD]
[TD]130[/TD]
[TD]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GSI Stacking Mug[/TD]
[TD]80[/TD]
[TD]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Long Handle Spork w/ biner[/TD]
[TD]80[/TD]
[TD]69[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nalgene 500ml[/TD]
[TD]100[/TD]
[TD]83[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Platypus 1L[/TD]
[TD]50[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Individual First Aid Kit[/TD]
[TD]130[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Triangular Bandage[/TD]
[TD]130[/TD]
[TD]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Field Dressing[/TD]
[TD]130[/TD]
[TD]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ice Bag[/TD]
[TD]350[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Ideally the "missing items sheet" would be a list stating the title information for each box, then rows of missing items, all arranged from left sheets to right most sheets. Your example does most of this already.

Thanks!

 

[vcoolio]

Hello Bandit,

I think that the following may do the task for you:-
<c4,true,false)"

Sub Test()

Dim ws As Worksheet
Dim lr As Long, lr1 As Long, c As Range

Application.ScreenUpdating = False

For Each ws In Worksheets
If ws.Name <> "Missing" Then
lr1 = ws.Range("B" & Rows.Count).End(xlUp).Row
For i = 3 To lr1
If ws.Cells(i, 1).Value = "" Then ws.Cells(i, 1).Value = " "
Next i
End If
Next ws

Sheet57.UsedRange.Offset(2).ClearContents

For Each ws In Worksheets
If ws.Name <> "Missing" Then
ws.UsedRange.Offset(2).Copy
Sheet57.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll

lr = Sheet57.Range("A" & Rows.Count).End(xlUp).Row
Sheet57.Range("F4:F" & lr) = "=if(D4<c4,true,false)"

With Sheet57.[A2].CurrentRegion
.AutoFilter 6, False
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End If
Next ws

Sheet57.Range("F4:F" & lr) = "=IFERROR(SUM(C4-D4),"""")"

For Each c In Sheet57.Range("F4:F" & lr)
If c.Value < 1 Then
c.ClearContents
End If
Next c

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Apologies Bandit. I've had to wrap the code in Quote tags rather than Code tags as I'm having some real problems with this forum.

Here's the link to the sample with the code implemented:-

http://ge.tt/3n7Tvgp2

I hope that this helps.

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

 

[Bandittheone]

vcoolio,

Thanks so much! This works awesomely.

One last question, how would you exclude certain sheets? Like for example if I want the "missing" one to be sheet3 and only pull from sheet4, 5, 6, etc, not sheet1 or 2.

Thanks again!

 

[vcoolio]

Hello Bandit,

You're welcome. I'm glad that its all sorted for you now.

Looking back at my last post, I just realised that, even using quote tags instead of code tags, half of the first formula was still cut off! I have no idea what's going on as this is only happening to me on this forum. However, I assume that you have been able to retrieve the whole code from the module in the sample.

One last question, how would you exclude certain sheets? Like for example if I want the "missing" one to be sheet3 and only pull from sheet4, 5, 6, etc, not sheet1 or 2.

Sheet "Missing" is already excluded from the whole process as it is the main "Summary" sheet to which all the relevant data is transferred.
As for excluding more sheets from the whole process, wherever you see this line in the code (its used twice):-

If ws.Name <> "Missing" Then

just add the other sheets that need to be excluded as follows:-

If ws.Name <> "Missing" And ws.Name <> "Whatever" And ws.Name <> "Whatever"  etc., etc...Then

Replace "Whatever" with the actual sheet name.

Cheerio,
vcoolio.

 

[Bandittheone]

Thanks again!

I added all of my boxes to your version and found that when I had too many sheets it would start copying every line, not just the missing items, and just by reducing the number of total sheets this was corrected.

Any thoughts?

 

[vcoolio]

Did you alter the code at all?

 

[Bandittheone]

Nope, other than adding the exclusions for the additional sheets.

Here is a screenshot showing a small portion of the lines added:

https://1drv.ms/u/s!AizpA15wjAfHjcAqVarG56lrkwVGAA

The debugger errored out on this line "Sheet57.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll"

 

[Bandittheone]

Okay, the error ""Sheet57.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll"" no longer happens after I cleared the "missing" sheet of it's copied over content. I think it was just one sheet with different formatting that was causing that. However the missing sheet is still copying over every single line from every sheet with the missing column only displaying "=IFERROR(SUM(C4-D4),"")"