Stock Control Workbook

[wtom0412]

Good Afternoon All,

I am happy to report that as a direct result of all of the amazing help I get from you Guru's at MrExcel, I am needing to use the resource much less these days, however, this one has me stumped!!

I am creating a Stock Control Workbook.

As per the following image, I have a "Home" TAB, which gets user input.

Excel 2012

	C	D	E	F	G	H
10	Quantity	Item	Transfer From	Transfer To	Stock at ADFA	Stock at Amberley - Canteen
11	2	Chef's Jacket - L	ADFA	Amberley - Canteen

<tbody>
</tbody>

Home

I want to be able to have the stock list reflect the changes as per the user selection above.

Both the Location Names and the Item Names are dynamic in that they can change as locations and items get added or removed. By this I mean that if ADFA (B1) gets removed as a Location, Amberley - C17 will automatically move to Column B, and similarly, if "Black Cap" (A3) gets deleted as a stock item, "Blue and White Stripped Apron" will automatically go into line A3. So, I am guessing the references to Location and Item will need to use the Label as opposed to the fixed Column and Line numbers?

The next image is the "Stock List" TAB. As per the above example, I need VBA to reduce the quantity of "Chef's Jacket - L" from the the "ADFA" column by 2 (B9), and increase the the quantity in "Amberley - Canteen" by 2 (D9).

Excel 2012

	A	B	C	D	E	F
1	Item and Location	ADFA	Amberley - C17	Amberley - Canteen	Borella	Canungara
2	Charcoal Apron	0	0	0	0	0
3	Black Cap	0	0	0	0	0
4	Blue & White Striped Apron	0	0	0	0	0
5	Blue & White Striped Hat	0	0	0	0	0
6	Chef's Jacket - 2XL	0	0	0	0	0
7	Chef's Jacket - 3XL	3	3	2	3	3
8	Chef's Jacket - 4XL	1	1	1	1	1
9	Chef's Jacket - L	2	2	2	2	2
10	Chef's Jacket - M	6	6	6	6	6
11	Chef's Jacket - S	4	4	4	4	4
12	Chef's Jacket - XL	1	1	1	1	1
13	Chef's Pants - 2XL	1	1	1	1	1
14	Chef's Pants - 3XL	0	0	0	0	0
15	Chef's Pants - 4XL	1	1	1	1	1
16	Chef's Pants - L	4	4	4	4	4
17	Chef's Pants - M	5	5	5	5	5
18	Chef's Pants - S	2	2	2	2	2
19	Chef's Pants - XL	4	4	4	4	4
20	Chef's White Hat	0	0	0	0	0

<tbody>
</tbody>

Stock List

I also have an "Add Stock" section on the Home TAB, at the moment (until I get this current VBA sorted), it adds the stock to the "Lists" TAB. The following code adds the item to the stock list and also to a running stock count list.

Sub Add_New_Stock()


    Dim lastrow As Long


Application.ScreenUpdating = False


'Checks to see if Item already exists in Stock List
Dim FindString As String
Dim rng As Range
FindString = Range("C5").Value
If Trim(FindString) <> "" Then
    With Sheets("Lists").Range("A:A") 'searches all of column A
        Set rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not rng Is Nothing Then
            MsgBox "     Sorry, this code name already exists in the STOCK LIST." & vbNewLine & vbNewLine & "                             Please enter a unique Item"  'value  found
            Exit Sub
        Else
' If Item does not exist in stock list, Adds Item to the Running Stock Count, Adds the Item to the Stocl List, Sorts List Alphabetically and Deletes Duplicates


' This Adds the item to the Running Stock Count
Sheets("Home").Range("C5:D5").Copy
With Sheets("Lists").Range("G" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteValues
End With


' This adds the item to the STOCK LIST
Sheets("Home").Range("S5:U5").Copy
With Sheets("Lists").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteValues
End With


' This copies the formula from the line above and pastes in; The formula =SUMIF(Tally,A7,Tally_Quantity) keeps a running tally of additions
Dim Col As Range
  For Each Col In Range("D:E").Columns
    With Cells(Rows.Count, Col.Column).End(xlUp)
      .Copy .Offset(1)
      .Value = .Value
    End With
  Next


' Sort_Product_Category Macro
    Sheets("Lists").Select
    Columns("A:E").Select
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Add Key:=Range("A2:A500") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Lists").Sort
        .SetRange Range("A2:A500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Remove_Duplicates
    Sheets("Lists").Select
    Columns("A:E").Select
    ActiveSheet.Range("$A$1:$E$5000").RemoveDuplicates Columns:=1, Header:=xlYes




Worksheets("Home").Activate


Application.CutCopyMode = False
Application.ScreenUpdating = True


End If
    End With
End If


 
End Sub

I really hope someone can help me, I am getting a bit stir crazy (been trying to figure this out for two days!!). I am sure I have left a few things out in my explanation, but I hope you understand what I am trying to achieve.

Thank you in advance.

WT