Summarization of a master Stock count / Inventory report, to produce several smaller reports.

warrima

New Member
Joined
Mar 10, 2009
Messages
37
Office Version
  1. 2007
Platform
  1. Windows
I have a stock count sheet with all the material we have in our warehouse. Once a month i do a stock count against the month starting total. I then go through each variance and write down what jobs used how much in a column ie 53067 (2) 53297 (3) and so one down the list. List is about 500 items long and typically there is around 10 -15 jobs open a month. Report is sent to buyers.

A summarized version then needs created for finance that then lists all the material used against each job separately. They then add cost data etc. Currently i compile the second report manually from the first. However its time consuming and easy to make mistakes.

I've tried using pivot tables but i cant get them to work. Im open to changing the main report but any changes would need to be easily reversible as the format is a standard that is used. Are there any formula that can help me with this. Ive played around with vlookup and match but having limited success.

Many thanks for any suggestions

stock count.png

sc used.png
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,612
Thanks for that.

How are we looking?

VBA Code:
Public lastrow As Long
Public lastcol As Long
Public ws As Worksheet
Public rownum As Long
Public colnum As Long

Sub CreateSummary()

Dim rownum2 As Long
Dim ws2 As Worksheet

Application.DisplayAlerts = False

For Each Sheet In ThisWorkbook.Worksheets
    If Sheet.Name = "FinanceSummary" Then
        Sheet.Delete
    End If
Next

Set ws = Sheets("Raw Material Inventory")
Sheets.Add.Name = "FinanceSummary"
Set ws2 = Sheets("FinanceSummary")

lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
rownum = 3
colnum = 9
rownum2 = 2

ws.Range("B2:H2").Copy ws2.Range("B1")
ws2.Range("A1") = "JN"
ws2.Range("I1") = "Qty. Used"

Do Until rownum = lastrow + 1
     Do Until ws.Cells(2, colnum) = "Used At Saw/Job"
        If ws.Cells(rownum, colnum) <> "" Then
            ws.Range(ws.Cells(rownum, 2), ws.Cells(rownum, 8)).Copy ws2.Cells(rownum2, 2)
            ws2.Cells(rownum2, 9) = ws.Cells(rownum, colnum)
            ws2.Cells(rownum2, 1) = ws.Cells(2, colnum)
            rownum2 = rownum2 + 1
        End If
    colnum = colnum + 1
    Loop
colnum = 9
rownum = rownum + 1
Loop
        
Application.DisplayAlerts = True

''FillinJN

End Sub
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,612
And to add those JN Strings to the last column.


VBA Code:
Public lastrow As Long
Public lastcol As Long
Public ws As Worksheet
Public rownum As Long
Public colnum As Long

Sub CreateSummary()

Dim rownum2 As Long
Dim ws2 As Worksheet

Application.DisplayAlerts = False

For Each Sheet In ThisWorkbook.Worksheets
    If Sheet.Name = "FinanceSummary" Then
        Sheet.Delete
    End If
Next

Set ws = Sheets("Raw Material Inventory")
Sheets.Add.Name = "FinanceSummary"
Set ws2 = Sheets("FinanceSummary")

lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastcol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
rownum = 3
colnum = 9
rownum2 = 2

ws.Range("B2:H2").Copy ws2.Range("B1")
ws2.Range("A1") = "JN"
ws2.Range("I1") = "Qty. Used"

Do Until rownum = lastrow + 1
     Do Until ws.Cells(2, colnum) = "Used At Saw/Job"
        If ws.Cells(rownum, colnum) <> "" Then
            ws.Range(ws.Cells(rownum, 2), ws.Cells(rownum, 8)).Copy ws2.Cells(rownum2, 2)
            ws2.Cells(rownum2, 9) = ws.Cells(rownum, colnum)
            ws2.Cells(rownum2, 1) = ws.Cells(2, colnum)
            rownum2 = rownum2 + 1
        End If
    colnum = colnum + 1
    Loop
colnum = 9
rownum = rownum + 1
Loop
        
Application.DisplayAlerts = True

FillinJN

End Sub

Sub FillinJN()

rownum = 3
colnum = 9

ws.Cells(2, lastcol + 1) = "JN"

Do Until rownum = lastrow + 1
    Do Until ws.Cells(2, colnum) = "Used At Saw/Job"
        If ws.Cells(rownum, colnum) <> "" Then
            ws.Cells(rownum, lastcol + 1) = ws.Cells(rownum, lastcol + 1) & ws.Cells(2, colnum) & " (" & ws.Cells(rownum, colnum) & ") "
        End If
    colnum = colnum + 1
    Loop
colnum = 9
rownum = rownum + 1
Loop

End Sub
 

warrima

New Member
Joined
Mar 10, 2009
Messages
37
Office Version
  1. 2007
Platform
  1. Windows
Finance summary works great. many thanks. Getting an error on the JN code;

jn error.png
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,612

ADVERTISEMENT

Did you paste the full code in? What happens when you hover the mouse over lastcol and ws?
 

warrima

New Member
Joined
Mar 10, 2009
Messages
37
Office Version
  1. 2007
Platform
  1. Windows
I did paste in again to double check but i get same error.

WS.png
lastcol.png
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,612

ADVERTISEMENT

lastcol should not be 0 by then. Are these above both codes?

VBA Code:
Public lastrow As Long
Public lastcol As Long
Public ws As Worksheet
Public rownum As Long
Public colnum As Long
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,612
If they are then you might need to redeclare the variables:

VBA Code:
Sub FillinJN()

Dim lastcol As Long
Dim ws As Worksheet

Set ws = Sheets("Raw Material Inventory")
lastcol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column

rownum = 3
colnum = 9

ws.Cells(2, lastcol + 1) = "JN"

Do Until rownum = lastrow + 1
    Do Until ws.Cells(2, colnum) = "Used At Saw/Job"
        If ws.Cells(rownum, colnum) <> "" Then
            ws.Cells(rownum, lastcol + 1) = ws.Cells(rownum, lastcol + 1) & ws.Cells(2, colnum) & " (" & ws.Cells(rownum, colnum) & ") "
        End If
    colnum = colnum + 1
    Loop
colnum = 9
rownum = rownum + 1
Loop

End Sub
 

warrima

New Member
Joined
Mar 10, 2009
Messages
37
Office Version
  1. 2007
Platform
  1. Windows
Is this to do with the used at saw/job column? Its just a calc cell to sum all the materials in the jobs. I just subtract that from the Qty to give me the Calc count qty and to give me a status of OK if calc count and Count match.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,612
The saw/job column is just mentioned so the code knows when it has gone past the last job. Did the above code work?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,255
Messages
5,595,106
Members
413,970
Latest member
Hodg100

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
Top