Monthly Inventory

jdude

New Member
Joined
Jun 6, 2015
Messages
8
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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,
 
Upvote 0
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!
 
Upvote 0
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).
 
Upvote 0
2KLkP
 
Upvote 0
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.
 
Upvote 0
So there is no real way to use VBA to copy to subsequent sheets and keep the same format?
 
Upvote 0
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>
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top