MACRO Submit data drop to next row upon submission [Solved]

BMiller58 6 Posts Friday August 10, 2018Registration date August 11, 2018 Last seen - Aug 10, 2018 at 03:27 PM - Latest reply: vcoolio 1184 Posts Thursday July 24, 2014Registration dateModeratorStatus August 11, 2018 Last seen
- Aug 11, 2018 at 07:16 PM
Hello,

I am currently working on an Excel workbook that allows for a user to input specific information pertaining to WIP product, click the command button to submit, and then data to compile in a secondary spreadsheet; however, I do not know how to get input data to the next cell of the secondary spreadsheet without clearing out the original entry. I would like to keep a running log of entries in one master location so that we may effectively gain insight into tracking our WIP.

Here is the code:
Private Sub cbSubmit_Click()
Dim User As String, ProductScan As String, ScanFromLocation As String, ScanToLocation As String
Worksheets("WIP_Finder").Select
User = Range("B9")
ProductScan = Range("C9")
ScanFromLocation = Range("D9")
ScanToLocation = Range("E9")
Worksheets("WIP_History").Select
Worksheets("WIP_History").Range("A2").Select
If Worksheets("WIP_History").Range("A2").Offset(1, 0) <> "" Then
Worksheets("WIP_History").Range("A2").End(xlDown).Select
End If
ActiveCell.Offset(0, 0).Select
ActiveCell.Value = User
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProductScan
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ScanFromLocation
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ScanToLocation
Worksheets("WIP_Finder").Select
Worksheets("WIP_Finder").Range("B9:E9").ClearContents
Worksheets("WIP_Finder").Cells(Rows.Count, "A").End(xlDown).Offset(1, 0).PasteSpecial


Can you help me?

See more 

Your reply

11 replies

ac3mark 10284 Posts Monday June 3, 2013Registration dateModeratorStatus August 20, 2018 Last seen - Updated by ac3mark on 10/08/18 at 04:37 PM
0
Thank you
We aren't you using a database? That is what a database is for. Excel is for calculations, not storing of data. If you insist on using excel, then this should get you going:

Function countit(WhatSheet)
Z = Cells(ThisWorkbook.Worksheets(WhatSheet).Rows.Count, 1).End(xlUp).Row
countit = Z + 1
End Function



Now if you utilize with:

nret = countit("sheet1")
MsgBox (nret & " is the next empty row")


BMiller58 6 Posts Friday August 10, 2018Registration date August 11, 2018 Last seen - Aug 10, 2018 at 06:49 PM
Thank you for your prompt response!
If given the opportunity for a legitimate database, I would be all for it! Unfortunately, my organization does not share the same sentiment and would like for me develop something as a Band-Aid.
Let me try this out! :)
BMiller58 6 Posts Friday August 10, 2018Registration date August 11, 2018 Last seen - Aug 10, 2018 at 07:09 PM
The information provided does not currently work.
In relation to the original code, where would these suggested lines of code need to go?
Sorry for the trouble
ac3mark 10284 Posts Monday June 3, 2013Registration dateModeratorStatus August 20, 2018 Last seen - Aug 10, 2018 at 09:13 PM
No problem, we are here to help!

The band aid might be quicker applied with a DB if you are having issue deploying the above code!

Give us an example of the code you are using, and let us help you out. Do you get that this is a portable function that can be called over and over for any sheet that you wish to inspect (as long as it is in the current loaded workbook)?

What error do you get? What sheet name are you passing to the function? Is your sheet named Sheet1?
BMiller58 6 Posts Friday August 10, 2018Registration date August 11, 2018 Last seen - Aug 10, 2018 at 09:23 PM
Thank you for your patience; Not the strongest coder.
I have (2) Sheets; The first of which is titled "WIP_Finder", the "WIP_Finder" acts as the data source, which passes the data to the second sheet "WIP_History."
I am able to send the data from "WIP_Finder" to "WIP_History" and successfully clear the data from "WIP_Finder"; however, when I attempt to enter in new data, it wipes out my original entry in exchange for the new line of data.
If you are okay with it, I could send you the file(?) It may make a lot more sense to you
Respond to ac3mark
vcoolio 1184 Posts Thursday July 24, 2014Registration dateModeratorStatus August 11, 2018 Last seen - Aug 10, 2018 at 11:02 PM
0
Thank you
Hello BMiller58,

Upload a sample of your file to a free file sharing site such as ge.tt or Drop Box then post the link to your file back here. Make sure that the sample is an exact replica of your actual workbook and include the code that you are attempting to work with. If the data is sensitive then please use dummy data.

Just a few rows of data will suffice per sheet so that we can see what you are attempting to do.

Cheerio,
vcoolio.
Respond to vcoolio
vcoolio 1184 Posts Thursday July 24, 2014Registration dateModeratorStatus August 11, 2018 Last seen - Aug 11, 2018 at 02:39 AM
0
Thank you
Good day Mr. Miller,

You can trim the code right down to the following:-

Private Sub cbSubmit_Click()

    Dim ws As Worksheet: Set ws = Sheet1
    Dim sh As Worksheet: Set sh = Sheet2
 
Application.ScreenUpdating = False

    ws.[B9:E9].Copy
    sh.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
    ws.[B9:E9].ClearContents

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


Remove the current code that you have assigned to the button and replace it with the above.
I have used the sheet codes instead of the sheet names in the code above.

Test it in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.
vcoolio 1184 Posts Thursday July 24, 2014Registration dateModeratorStatus August 11, 2018 Last seen - Aug 11, 2018 at 06:54 AM
Sorry to bother you again but I've just noticed the time stamp column in the destination sheet.
If you'd like this done automatically with each transfer then, in the code above, add the following line of code:-

sh.Range("E" & Rows.Count).End(3)(2) = Format(Now()) '----> Permanent time stamp.


directly after this line of code:-

sh.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues


The time stamp is not volatile in this instance and will remain "frozen" from the time that the transfer is completed.

I hope that this helps.

Cheerio,
vcoolio.
BMiller58 6 Posts Friday August 10, 2018Registration date August 11, 2018 Last seen - Aug 11, 2018 at 11:36 AM
OMG! It works! Thank you so much vcoolio and ac3mark!
This is going to work out splendidly!
vcoolio 1184 Posts Thursday July 24, 2014Registration dateModeratorStatus August 11, 2018 Last seen - Aug 11, 2018 at 07:16 PM
You're welcome BMiller. I'm glad that we were able to help.

Cheerio,
vcoolio.
Respond to vcoolio