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

Posts
10
Registration date
Friday August 10, 2018
Last seen
October 7, 2018
-
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

3 replies

Posts
10934
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 11, 2018
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
Posts
10
Registration date
Friday August 10, 2018
Last seen
October 7, 2018
-
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
Posts
10
Registration date
Friday August 10, 2018
Last seen
October 7, 2018
-
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
Posts
10934
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 11, 2018
-
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
Posts
10
Registration date
Friday August 10, 2018
Last seen
October 7, 2018
-
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
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
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
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
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
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
-
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
Posts
10
Registration date
Friday August 10, 2018
Last seen
October 7, 2018
-
OMG! It works! Thank you so much vcoolio and ac3mark!
This is going to work out splendidly!
vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
-
You're welcome BMiller. I'm glad that we were able to help.

Cheerio,
vcoolio.
Respond to vcoolio