Monthly Inventory

[jdude]

Hello All

I am creating an inventory sheet for a restaurant. I am having an issue figuring out how to add inventory items and have them automatically remain (copy over) to the following months. Example: If I add "Mark West Pinot Noir" in June I would like it to show in July, August, and so on until the end of the year. Any help would be greatly appreciated. This is my first post to ANY forum so please forgive me if I am in the wrong place.

Thanks Again
Jdude

 

[Smitty]

Welcome to the Board!

If you put your inventory detail all on one sheet (think database structure), you can easily summarize by month with Pivot Tables.

HTH,

 

[jdude]

The issue is that I have 590 different wines. I have them categorized in tables but if buy a new item in March I want it to carry over to the following months (same workbook, separate sheets) so that I can continue to add sales and purchase data for the remainder of the year. I can send you the sheet if you would like in case im not explaining my issue completely. And thank you for your reply!

 

[Smitty]

Having data stored in separate worksheets makes it hard to do anything with, especially from a summary standpoint. In the long run you'll be much better off using a database type structure. You can easily add category types for each entry, which gives you more to summarize with pivot tables.

We don't support file uploads here, but if you follow the HTML Maker link in my sig you can post a screen shot(s).

 

[jdude]

https://www.dropbox.com/s/6vyy9sb1bpyi9a8/Screenshot 2015-06-08 13.36.11.png?dl=0

The link Shows January. If I add an item I would like February, March, April and so on to show the addition without having to manually add new items to each month.

 

[jdude]

 

[Smitty]

If I add an item I would like February, March, April and so on to show the addition without having to manually add new items to each month.

Then you'll need to use VBA to copy a row to each subsequent sheet.

But you're really making things hard on yourself if you keep this structure.

If MS Access is an option I'd lean in that direction, as it's much better suited to inventory management than Excel.

 

[jdude]

So there is no real way to use VBA to copy to subsequent sheets and keep the same format?

 

[Smitty]

The will paste the active cell's entire row to each worksheet other than itself and the Table of Contents:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsSource <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsSource = ActiveSheet<br>    wsSource.Cells(ActiveCell.Row, "A").EntireRow.Copy<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>        <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Table of Contents" And ws.Name <> ActiveSheet.Name <SPAN style="color:#00007F">Then</SPAN><br>            lr = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row<br>            ws.Cells(lr, "A").PasteSpecial xlPasteAll<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ws<br>            <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jdude]

You are so close!!! There is something that the sheet does not like about this code though. Are you opposed to me emailing you the sheet?