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

[warrima]

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

 

[warrima]

 

[mrshl9898]

Thanks for that.

How are we looking?

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]

And to add those JN Strings to the last column.

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]

Finance summary works great. many thanks. Getting an error on the JN code;

 

[mrshl9898]

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

 

[warrima]

I did paste in again to double check but i get same error.

 

[mrshl9898]

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

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

 

[mrshl9898]

If they are then you might need to redeclare the variables:

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]

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]

The saw/job column is just mentioned so the code knows when it has gone past the last job. Did the above code work?