Previous Topic Next Topic

MACRO submit data drop to next row upon submission

Solved/Closed
BMiller58 Posts 10 Registration date Friday August 10, 2018 Status Member Last seen October 7, 2018 - Updated on Sep 25, 2018 at 12:15 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - 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?

3 responses

Answer 1 / 3
Blocked Profile
Updated on Aug 10, 2018 at 04:37 PM
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")
0
BMiller58 Posts 10 Registration date Friday August 10, 2018 Status Member Last seen October 7, 2018
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! :)
0
BMiller58 Posts 10 Registration date Friday August 10, 2018 Status Member Last seen October 7, 2018
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
0
Blocked Profile
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?
0
BMiller58 Posts 10 Registration date Friday August 10, 2018 Status Member Last seen October 7, 2018
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
0
Answer 2 / 3
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Aug 10, 2018 at 11:02 PM
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.
0
BMiller58 Posts 10 Registration date Friday August 10, 2018 Status Member Last seen October 7, 2018
Aug 11, 2018 at 12:28 AM
https://www.dropbox.com/s/5t9xidgq9dfe2s1/WIP_Finder.xlsm?dl=0
0
Answer 3 / 3
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Aug 11, 2018 at 02:39 AM
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.
0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
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.
0
BMiller58 Posts 10 Registration date Friday August 10, 2018 Status Member Last seen October 7, 2018
Aug 11, 2018 at 11:36 AM
OMG! It works! Thank you so much vcoolio and ac3mark!
This is going to work out splendidly!
0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Aug 11, 2018 at 07:16 PM
You're welcome BMiller. I'm glad that we were able to help.

Cheerio,
vcoolio.
0

Similar discussions

Instagram Account Hacked!
artistfromNW3 -
Domme1 -

39 responses
Selfie video verification upload error
Soslilfrost -
Muaidxd -

18 responses
AMD data change update new data to DMI
Luis -
dwebb -

1 response
Facebook want to verify my ID
Hoopla1234 -
Rennosh -

9 responses
Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response