Creating a master inventory sheet and automatic history

[accesspoot]

I'm not sure which section to post this in, any advice on a more appropriate forum is appreciated.

I am trying to create a master inventory worksheet for a small business - one that can auto populate the data into a separate worksheet to analyze sales history and then re-clear the master sheet for the next entry. I have tried googling many different things but am not sure the exact specifics for what I would like to accomplish. I apologize if my post is confusing, I will do the best I can to clearly describe what I would like to accomplish.

For additional background - this small business has less than 75 total products and will be doing inventory daily. All products are counted in the same unit of measurement, individually packaged.

What I had in mind is to have a main 'Master Inventory' sheet with a complete list of products in Column A and Column B would remain blank to enter the current amount on hand. Once inventory is fully entered, the user would hit a command button ('Submit') at the bottom to automatically transfer the data to a second "Sales History" worksheet which would timestamp each inventory entry. The "Sales History" worksheet would contain the same information in Column A as the "Master Inventory" but would continue to add each new inventory entry, complete with timstamp, into the following column (column b, c, d, e, etc.).

Any advice on how to accomplish this? I've created command buttons before for similar functions, but have always copied the code from online and don't fully understand VBA yet. Thanks in advance!

 

[Logit]

.
This is real close to what you are seeking. Let me know if you require assistance changing anything.

Option Explicit


Sub Transpose_PasteModified()


Dim Source As Worksheet
Dim Destination As Worksheet
Dim EmptyColumn As Long


Set Source = Sheets("Sheet1")
Set Destination = Sheets("Sheet2")


On Error Resume Next
Application.ScreenUpdating = False


'Data Source
Source.Range("C2:C100").Copy    'copies C2:C100 Sheet1.   Edit range as required.


'Gets Empty Column
EmptyColumn = Destination.Cells(3, Destination.Columns.Count).End(xlToLeft).Column


'In order to avoid issues of first row returning 1
'in this case [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL]  is the row so we're checking A2
'If row changes then change A2 to correct row


    If IsEmpty(Destination.Range("A2")) Then   'pastes to Sheet 2 Col A unless Col A has rows full. Then
        Destination.Cells(2, 1).PasteSpecial   'it pastes to the first blank column
        Destination.Cells(2, 1).EntireColumn.AutoFit


    Else
        EmptyColumn = EmptyColumn + 1
        Destination.Cells(2, EmptyColumn).PasteSpecial
        Destination.Cells(2, EmptyColumn).EntireColumn.AutoFit


    End If


'uncomment the next line if you want Sheet 1, Column C cleared after copying data to Sheet 2
'Source.Range("C3:C100") = ""    'clears range "C2:C100" on Sheet 1


Sheets("Sheet2").Activate
Sheets("Sheet2").Range("A1").Select
Sheets("Sheet1").Activate
Sheets("Sheet1").Range("C3").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

You can download a copy of the workbook here : Amazon Drive

 

[accesspoot]

Thanks for the help! Unfortunately, this is still a bit over my head. I tried copying the code into the VBE for the worksheet and it didn't work for me. Do you mind walking me through it a bit more? The only command buttons I have created in the past I copied from online tutorials and tying that together with the code is causing me problems. Sorry for my inexperience! Thanks again!

 

[accesspoot]

I should also mention that I need this to run on an iPad and have only used ActiveX controls before.

 

[Logit]

.
If you are running it on an IPad, I doubt it will function as desired. Apple does things differently than PC's.

If you download the actual workbook from the link provided and can run it on a PC, you will be able to review the code and where the code is placed in the workbook.