Creating Stock in and out data forms to take or remove quantities from an inventory sheet

**HarrietEllen** · 10-06-2018, 04:38 PM

Hi guys, I'm new here but desperately looking for some help after youtube tutorials have come up short.

I'm creating an inventory management system where the basis is a 'current stock' sheet in a pivot table that will be filled with all the items.
excel1.png

I figured out how to create a data entry form on another sheet by recording the macro in this sheet and then linking that macro to a button
excel2.png

So the data entry would go into the original stock sheet in image 1, but I tested it on a separate sheet as so
excel3.png

However every time I fill out the form in the data entry sheet in image 2, it overwrites the same row (seen in row 5 in example), even though I pressed ctrl and down key when recording my macro, how do I stop this?

Other than this I'd like to also know, is there away so instead of adding new data to the current stock sheet using the forms, can I create stock in and stock out forms that just edit existing data on the sheet? If so could someone please help me with this?
I'd be happy to paypal a little cash in exchange for some time and would be eternally greatful, thanks for reading!

Harriet

**Joske920** · 10-06-2018, 05:03 PM

Hi HarrietEllen,

Can you post the macro you recorded?
We can clean it up a bit

dont forget to click the # for the code tags the Mods will be happy that way

Also Ctrl + down puts you on the last row and you need the row under that

Grtz

**HarrietEllen** · 10-06-2018, 07:24 PM

HI Joske920, Thanks for replying! I had no idea I thought it would take me the row under that, silly me.
Below is the code for the macro I recorded, I hope you can think of something to do with it

Sub MoveStockToTable()
'
' MoveStockToTable Macro
' Moves
'

'
    Range("C4").Select
    Selection.Copy
    Sheets("Sheet4").Select
    Range("A3").Select
    Selection.End(xlDown).Select
    Range("A5").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Stock In").Select
    Range("C5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet4").Select
    Range("B5").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Stock In").Select
    Range("C6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet4").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Stock In").Select
    Range("C7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet4").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Stock In").Select
    Range("C8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet4").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Stock In").Select
    Range("C9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet4").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 2).Range("A1").Select
    Sheets("Stock In").Select
    Range("C10").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C9").Select
    Selection.ClearContents
    Range("C8").Select
    Selection.ClearContents
    Range("C7").Select
    Selection.ClearContents
    Range("C6").Select
    Selection.ClearContents
    Range("C5").Select
    Selection.ClearContents
    Range("C4").Select
    Selection.ClearContents
End Sub

**Joske920** · 10-07-2018, 03:09 AM

Hi HarrietEllen,

I changed it to this, wich does exact the same exept the row mistake offcourse

Sub MoveStockToTable()
'
' MoveStockToTable Macro
' Moves
'
Dim arr(7) As Variant, rng As Range, cel As Range, I As Long
' select input
    Sheets("Stock In").Activate
    Set rng = Range("C4:C9")
    'get data from input
    I = 0
    For Each cel In rng
       arr(I) = cel.Value
       I = I + 1
    Next cel
    
    'Go to Database
    Worksheets("Sheet4").Activate
    'find first empty row after Header row
    I = Worksheets("Sheet4").Columns(1).Find(What:=vbNullString, Lookat:=xlWhole, After:=Worksheets("Sheet4").Range("A3")).Row
    Set rng = Range(Cells(I, 1), Cells(I, 6))
    'put collected data in found open row
    I = 0
    For Each cel In rng
       cel = arr(I)
       I = I + 1
    Next cel

End Sub

But i made an effort to make it a bit smarter with updating the Stock by searching the ID first and if its in the data just add to the stock


Sub MoveStockToTable()
'
' MoveStockToTable Macro
' Moves
' checks for existing ID
Dim arr(7) As Variant, rng As Range, cel As Range, I As Long, DataExists As Boolean
' select input
    Sheets("Stock In").Activate
    Set rng = Range("C4:C9")
    'get data from input
    I = 0
    For Each cel In rng
       arr(I) = cel.Value
       I = I + 1
    Next cel
    
    'Go to Database
    Worksheets("Sheet4").Activate
    'If Id already in data just add to stock
    On Error Resume Next
    I = Worksheets("Sheet4").Columns(1).Find(What:=arr(0), Lookat:=xlWhole, After:=Worksheets("Sheet4").Range("A3")).Row
    On Error GoTo 0
    If I = 0 Then
       'find first empty row after Header row
       I = Worksheets("Sheet4").Columns(1).Find(What:=vbNullString, Lookat:=xlWhole, After:=Worksheets("Sheet4").Range("A3")).Row
      Else
       DataExists = True
    End If
    
    If DataExists Then
       'if ID found only update Stock
       Set cel = Range(Cells(I, 6), Cells(I, 6))
       cel.Value = cel.Value + arr(5)
      Else
       Set rng = Range(Cells(I, 1), Cells(I, 6))
       'put collected data in found open row
       I = 0
       For Each cel In rng
          cel = arr(I)
          I = I + 1
       Next cel
    End If
    
End Sub

I hope you can use it.

ps.
When adding this new code in the same page you might want to change the name of the old macro

**HarrietEllen** · 11-04-2018, 08:47 PM

Hi Joske, sorry it's been almost a month, life has been crazy busy. I just used this for the project i was working on and it was great. Thanks so much for your help!!
If you have a paypal send me your address and i will send you a little money to say thanks if you like.

Creating Stock in and out data forms to take or remove quantities from an inventory sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Creating Stock in and out data forms to take or remove quantities from an inventory sheet

Re: Creating Stock in and out data forms to take or remove quantities from an inventory sh

Re: Creating Stock in and out data forms to take or remove quantities from an inventory sh

Re: Creating Stock in and out data forms to take or remove quantities from an inventory sh

Re: Creating Stock in and out data forms to take or remove quantities from an inventory sh

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Looking for help on a Loop to find and update stock sheet quantities

Creating a basic stock inventory sheet

I need a stock order sheet to be created from a stock inventory

Creating User-Friendly Forms for Inventory Entry

Creating an automatic stock list/inventory, HELP!

Inventory Stock Balancing with Inventory Data

[SOLVED] Are there any inventory forms that change quantities as needed?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions