Continuous Stock file

utkarsh4rush

New member
Joined
Jun 20, 2018
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2007
I have made a file where Sheet 1 represents the first day of the month and so on. Each sheet has the list of products and the opening stock for the day, and the daily production and sales are added, resulting to a closing stock.

Now, i want that every time i make a new sheet, the new opening stock is automatically equal to the closing stock of the previous day and all other counters are automatically zero. Also, suppose a new product is added on say 20th of the month, it is reflected through all the previous sheets (Interlinking all the sheets where changes to one sheet is reflected to others and vice-versa.

Also, ive named my sheets 20180601, 20180602 and so on. So is there a way where every time i make a new sheet, its name changes to the next date automatically? (Not important though)

I want to know if the above is possible. Though i know most of the excels, i have zero knowledge about macros and VBAs. (So please explain it to me in layman language
frown.gif
confused.gif
)

I have attached a dummy sheet of what i made
 

Attachments

  • Inventory Dummy.xlsx
    12.6 KB · Views: 14
.
Paste this into a ROUTINE MODULE :

Code:
Option Explicit
Sub CreateSheets()
    Const lngFirstWorkDay = vbMonday
    Dim lngYear As Long
    Dim lngMonth As Long
    Dim lngOption As Long
    Dim rngHolidays As Range
    Dim d As Date
    Dim wsh As Worksheet
    Dim f As Boolean
    lngYear = Val(InputBox("For which year do you want to create sheets?"))
    
    If lngYear < 2000 Or lngYear > 2100 Then
        MsgBox "Year not valid! Please try again.", vbInformation
        Exit Sub
    End If
    
    lngMonth = Val(InputBox("For which month (1 ... 12) do you want to create sheets?"))
    
    If lngMonth < 1 Or lngMonth > 12 Then
        MsgBox "Month not valid! Please try again.", vbInformation
        Exit Sub
    End If
    
    lngOption = 1
    
    If lngOption < 1 Or lngOption > 1 Then
        MsgBox "Option not valid! Please try again.", vbInformation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    d = DateSerial(lngYear, lngMonth, 1)
    
    Do
        f = True
        Sheets("Template").Range("A1:Z100").Copy    'adjust range in Template to copy here
        If f Then
            Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            wsh.Name = Format(d, "yyyy_mm_dd")
            Range("A1").PasteSpecial xlPasteAll
            Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
            Range("A1").Select
        End If
        
        d = d + 1
    
    Loop Until Month(d) <> lngMonth
    Sheets("Template").Activate
    Sheets("Template").Range("A1").Select
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
End Sub

Paste this as well into a separate ROUTINE MODULE :

Code:
Option Explicit


Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then _
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
End Function

Refer to the attached completed workbook. You will notice there is a TEMPLATE sheet. This is the sheet the code is referencing for content and format. The code
presents two INPUT BOXES. First one asks for the year .. the second Input Box asks for which month. Then it creates all of the sheets for that month.

The TEMPLATE has new formulas in the OPENING STOCK column : =PrevSheet(K2) This formula copies the data present in the previous worksheet / cell indicated and pastes same into
the new blank sheet. So each month it automatically copies the CLOSING STOCK data from the previous worksheet and moves it to the new month blank sheet to the OPENING STOCK column.

You will notice that SHEET1 has the CLOSING STOCK data you posted in your example file. You can change those numbers if needed, BUT DO NOT DELETE Sheet1 ... as the first sheet for the month you create
will copy those numbers for the OPENING STOCK numbers.

Let me know if this helps.
 

Attachments

  • Create Sheets for Days of Month.xlsm
    28.2 KB · Views: 7
Thank you so much!!! This thing works better than what i could have expected.

I just have another query: Suppose i make a new product on say 18th June and add the product in the same sheet. Can it reflect across all other sheets (On the 21st, 22nd and so on)?
Currently ive made a template sheet and all the new sheets are linked to it. So, whenever there is a new product, i add the product to the template sheet and it is added to all the sheets. But i want to make it easier if possible. The thing is, whenever i enter the new product in the template, i have to drag the cells in all the sheets. And that is creating a lot of problems. The users of this sheet wont be able to understand and spoil all the data.

PS: Im making this sheet for people who wont be knowing excel at all. They are unskilled labours, so the easier it is, better for them.
 
Last edited:
For now, i made a new sheet Index with all the products.
Then, I referenced the first 4 columns of "Template" from Index. ( Template!B2=INDEX!B2)
This is how it looks (Lots of zeros)
Then, I dragged the formula (In template)Snip_Excel.PNG to some 20-30 rows below the last product (So, that i dont have to drag and drop all the time for each product)
Then i ran that macro.

Is there a more systematic approach? This one is doing fine but its not as "clean" as how you made the above sheet.
 
It will be best if the changes made are only applied to the next sheets and not the previous ones.

Thank you in advance
 
.
Extending the formula rows as you have done (highlight the row then drag down) is appropriate. You could probably create some code or a formula but the manual way is just as effective.

I've amended the code in the workbook to accomplish adding new stock. You'll find a separate sheet entitled "Add New Stock" that is set aside for this purpose only. It should be
fairly self explanatory. Enter the data in the first four columns / row 2. Select the sheet that will be the first sheet where the new stock is pasted to. Then click the button.

Easy peasy ( I hope ). Can't get much simpler than that for the employees ?

Let me know your thoughts.

Code:
Option Explicit
Sub CreateSheets()
    Const lngFirstWorkDay = vbMonday
    Dim lngYear As Long
    Dim lngMonth As Long
    Dim lngOption As Long
    Dim rngHolidays As Range
    Dim d As Date
    Dim wsh As Worksheet
    Dim f As Boolean
    lngYear = Val(InputBox("For which year do you want to create sheets?"))
    
    If lngYear < 2000 Or lngYear > 2100 Then
        MsgBox "Year not valid! Please try again.", vbInformation
        Exit Sub
    End If
    
    lngMonth = Val(InputBox("For which month (1 ... 12) do you want to create sheets?"))
    
    If lngMonth < 1 Or lngMonth > 12 Then
        MsgBox "Month not valid! Please try again.", vbInformation
        Exit Sub
    End If
    
    lngOption = 1
    
    If lngOption < 1 Or lngOption > 1 Then
        MsgBox "Option not valid! Please try again.", vbInformation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    d = DateSerial(lngYear, lngMonth, 1)
    
    Do
        f = True
        Sheets("Template").Range("A1:Z100").Copy    'adjust range in Template to copy here
        If f Then
            Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            wsh.Name = Format(d, "yyyy_mm_dd")
            Range("A1").PasteSpecial xlPasteAll
            Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
            Range("A1").Select
        End If
        
        d = d + 1
    
    Loop Until Month(d) <> lngMonth
    Sheets("Template").Activate
    Sheets("Template").Range("A1").Select
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
    
    MsgBox "All Sheets Created", vbExclamation, "Sheet Creation"
    
End Sub


Sub SheetsToCopyTo()
Dim ws As Worksheet
Dim ArrayElement As Variant 'loop through all elements in array
Dim DoNotCopyTo(0 To 10) As String 'Used to store NAMES of worksheets
Dim Found As Boolean 'test if worksheet found in array
Dim x As Variant


'string values
DoNotCopyTo(0) = "Template"
DoNotCopyTo(1) = "Add New Stock"
DoNotCopyTo(2) = "Sheet2"




For Each ws In Worksheets
    Found = False
    For Each ArrayElement In DoNotCopyTo 'if worksheet name found
        If ws.Name = ArrayElement Then 'If Found set to true and exit loop
            Found = True
            Exit For
        End If
    Next ArrayElement
    If Found = False Then   'If not in array, copy new data to it.
        If ws.Name > Range("H2").Value Then
         ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3).Value = Range("A2:C2").Value
        End If
        If ws.Name = Range("H2").Value Then
            ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Sheets("Add New Stock").Range("A2:D2").Value
        End If
    End If
Next ws
      
Sheets("Add New Stock").Range("A2:D2").ClearContents


MsgBox "Add New Stock Completed.", vbExclamation, "Add New Stock"


End Sub

The FUNCTION in Module 2 remains the same.
 

Attachments

  • Create Sheets for Days of Month revised 1.xlsm
    73.1 KB · Views: 9
This is doing whatever I wanted. Thanks a lot :)
But, actually I changed my sheet a little, added "S.no" column (A) and another column (B). I tried changing the code but I'm unable to do it. Whatever I do, it starts adding the new product, but through all the sheets from day 1 to 30. Also, its being added to just the first 3 columns. I can attach the code I made tomorrow as I did the changes in my work PC. I remember I changed this: "ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Range("B2:F2").Value" and "ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 5).Value = Sheets("Add New Stock").Range("A2:E2").Va" and "Sheets("Add New Stock").Range("A2:E2").ClearContents"

(This is what I think I did, I will send the full code tomorrow)
 
Option Explicit
Sub CreateSheets()
Const lngFirstWorkDay = vbMonday
Dim lngYear As Long
Dim lngMonth As Long
Dim lngOption As Long
Dim rngHolidays As Range
Dim d As Date
Dim wsh As Worksheet
Dim f As Boolean
lngYear = Val(InputBox("For which year do you want to create sheets?"))

If lngYear < 2000 Or lngYear > 2100 Then
MsgBox "Year not valid! Please try again.", vbInformation
Exit Sub
End If

lngMonth = Val(InputBox("For which month (1 ... 12) do you want to create sheets?"))

If lngMonth < 1 Or lngMonth > 12 Then
MsgBox "Month not valid! Please try again.", vbInformation
Exit Sub
End If

lngOption = 1

If lngOption < 1 Or lngOption > 1 Then
MsgBox "Option not valid! Please try again.", vbInformation
Exit Sub
End If

Application.ScreenUpdating = False

d = DateSerial(lngYear, lngMonth, 1)

Do
f = True
Sheets("Template").Range("A1:Z100").Copy 'adjust range in Template to copy here
If f Then
Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
wsh.Name = Format(d, "yyyy_mm_dd")
Range("A1").PasteSpecial xlPasteAll
Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
Range("A1").Select
End If

d = d + 1

Loop Until Month(d) <> lngMonth
Sheets("Template").Activate
Sheets("Template").Range("A1").Select
Application.CutCopyMode = False

Application.ScreenUpdating = True

MsgBox "All Sheets Created", vbExclamation,
End Sub


'Sub AddValues2()
'Dim ws As Worksheet
'Dim x As Variant

'x = Array("Template", "Add New Stock", "Sheet2")

'For Each ws In Worksheets
'If ws.Name >= Range("H2").Value And Not ws.Name = x Then 'And Ws.Name <= Range("I2").Value Then
'ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Range("A2:D2").Value
'End If
'Next ws
'End Sub


Sub SheetsToCopyTo()
Dim ws As Worksheet
Dim ArrayElement As Variant 'loop through all elements in array
Dim DoNotCopyTo(0 To 10) As String 'Used to store NAMES of worksheets
Dim Found As Boolean 'test if worksheet found in array
Dim x As Variant


'string values


DoNotCopyTo(0) = "Changes"
DoNotCopyTo(1) = "INDEX"
DoNotCopyTo(2) = "Add New Stock"
DoNotCopyTo(3) = "Template"
DoNotCopyTo(4) = "Sheet1"
'DoNotCopyTo(3) = ""
'DoNotCopyTo(4) = ""
'DoNotCopyTo(5) = ""
'DoNotCopyTo(6) = ""
'DoNotCopyTo(7) = ""
'DoNotCopyTo(8) = ""
'DoNotCopyTo(9) = ""
'DoNotCopyTo(10) = ""


'LastLine = Range("A" & Rows.Count).End(xlUp).Row




For Each ws In Worksheets
Found = False
For Each ArrayElement In DoNotCopyTo 'if worksheet name found
If ws.Name = ArrayElement Then 'If Found set to true and exit loop
Found = True
Exit For
End If
Next ArrayElement
If Found = False Then 'If not in array, copy new data to it.
If ws.Name > Range("H2").Value Then
ws.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Range("A2:E2").Value
End If
If ws.Name = Range("H2").Value Then
ws.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 5).Value = Sheets("Add New Stock").Range("A2:E2").Value
End If
End If
Next ws

Sheets("Add New Stock").Range("A2:E2").ClearContents
End Sub


Capture.PNG

This is whats happening in all the sheets. I just noticed that the data is being added to the very first sheet (Changes) too, and so on in all the sheets. In this cases, i had selected the date 15/06/2018 to enter the data from. ive added 2 new columns, S.no (which increases with evry entry) and Capacity
 
Back
Top