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
 

warrima

New Member
Joined
Mar 10, 2009
Messages
37
Office Version
  1. 2007
Platform
  1. Windows
if i paste in straight it says Ambiguous name for rownum
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

warrima

New Member
Joined
Mar 10, 2009
Messages
37
Office Version
  1. 2007
Platform
  1. Windows
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
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,650
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
 

warrima

New Member
Joined
Mar 10, 2009
Messages
37
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Many thanks for this. Great solution.
 

warrima

New Member
Joined
Mar 10, 2009
Messages
37
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,650
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
 

warrima

New Member
Joined
Mar 10, 2009
Messages
37
Office Version
  1. 2007
Platform
  1. Windows
Brilliant works perfectly. Many Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,229
Messages
5,600,420
Members
414,383
Latest member
kevinlarey

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