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
 
if i paste in straight it says Ambiguous name for rownum
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
with

Public lastrow As Long
Public lastcol As Long
Public ws As Worksheet
Public rownum As Long
Public colnum As Long

at the top it has the error as before buy colnum = 9
 
Upvote 0
Not sure what the issue is...

Best to remove the Publics all together.

VBA Code:
Sub CreateSummary()

Dim lastrow As Long
Dim lastcol As Long
Dim ws As Worksheet
Dim rownum As Long
Dim colnum As Long
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()

Dim lastrowx As Long
Dim rownumx As Long
Dim colnumx As Long
Dim lastcolx As Long
Dim wsx As Worksheet

Set wsx = Sheets("Raw Material Inventory")
lastcolx = wsx.Cells(2, wsx.Columns.Count).End(xlToLeft).Column
lastrowx = wsx.Cells(wsx.Rows.Count, "A").End(xlUp).Row

rownumx = 3
colnumx = 9

wsx.Cells(2, lastcolx + 1) = "JN"

Do Until rownumx = lastrowx + 1
    Do Until wsx.Cells(2, colnumx) = "Used At Saw/Job"
        If wsx.Cells(rownumx, colnumx) <> "" Then
            wsx.Cells(rownumx, lastcolx + 1) = wsx.Cells(rownumx, lastcolx + 1) & wsx.Cells(2, colnumx) & " (" & wsx.Cells(rownumx, colnumx) & ") "
        End If
    colnumx = colnumx + 1
    Loop
colnumx = 9
rownumx = rownumx + 1
Loop

End Sub
 
Upvote 0
Hi mrshl9898, reports going great. just have one question if its possible. To finish the finance summary i need to add the cost of the material used against each row. Could this be done with the code? Easy enough to calculate but just thought would be a really clean solution if it was done when the report VBE is run.

The value each is in column AK x column AP (exchange to local currency) x Qty used in summary.


1597109546785.png
 
Upvote 0
Untested, try this:

VBA Code:
Sub CreateSummary()

Dim lastrow As Long
Dim lastcol As Long
Dim ws As Worksheet
Dim rownum As Long
Dim colnum As Long
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)
            ws2.Cells(rownum2, 10) = ws.Cells(rownum, colnum).Value * ws.Cells(rownum, 37).Value * ws.Cells(rownum, 42).Value
            rownum2 = rownum2 + 1
        End If
    colnum = colnum + 1
    Loop
colnum = 9
rownum = rownum + 1
Loop
     
Application.DisplayAlerts = True

FillinJN

End Sub

Sub FillinJN()

Dim lastrowx As Long
Dim rownumx As Long
Dim colnumx As Long
Dim lastcolx As Long
Dim wsx As Worksheet

Set wsx = Sheets("Raw Material Inventory")
lastcolx = wsx.Cells(2, wsx.Columns.Count).End(xlToLeft).Column
lastrowx = wsx.Cells(wsx.Rows.Count, "A").End(xlUp).Row

rownumx = 3
colnumx = 9

wsx.Cells(2, lastcolx + 1) = "JN"

Do Until rownumx = lastrowx + 1
    Do Until wsx.Cells(2, colnumx) = "Used At Saw/Job"
        If wsx.Cells(rownumx, colnumx) <> "" Then
            wsx.Cells(rownumx, lastcolx + 1) = wsx.Cells(rownumx, lastcolx + 1) & wsx.Cells(2, colnumx) & " (" & wsx.Cells(rownumx, colnumx) & ") "
        End If
    colnumx = colnumx + 1
    Loop
colnumx = 9
rownumx = rownumx + 1
Loop

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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