Log In
Register
Home
June 1, 2020
Offline
Okay, I am unable to provide a sample workbook because the file is too large so I will try my best to explain. I am creating shipping tickets for work,
Two sheets. We will say Sheet 1 (S1) and Sheet 2 (S2). Sheet 1 is where all the information is, and Sheet 2 is the ticket sheet itself where we can extract information from Sheet 1 to create our ticket.
On Sheet 1 there are 4 significant columns:
- Column A: Every row (we can assume up to 500 rows) has a drop down list with one option of "Yes"
- Column B: Every row is populated with a unique number that refers to the project number
- Column C: This column refers to the Shipping #... this used to be manual entry, but we are trying to automate this.. I will explain later. Assume every row on this column empty. What is populated in each row in this column comes from a cell in Sheet 2.
- Column D: This column refers to the Shipment Date. This has the same properties as Column C, and we are also trying to automate this.
Now the process of making the ticket...
Once a row, or project, is tagged "Yes" in column A (S1), we can go to S2 and sort and show all the "Yes" rows to make the ticket. Once this is done, we will see the ticket format with the project information in it (S2). When this is done, we have formulas that generate the Shipping # and Shipment date on S2, lets assume this content was populated on cells K8 and K9 respectively. Now that S2 is filled up, we have our ticket.
What we want...
Now that we have the Shipping # and Shipment date on S2, we want to put this information automatically in S1 to the correct columns (Columns C and D) ONLY for the rows that were tagged "Yes" for that specific Shipping # and Shipment date. Even once that row is untagged as "Yes", I still want the date and Shipping # to stay there. I need help to make a code that automates this. The operation of the user would be to make the ticket then activate this macro through a command button.
I have tried an autofilter, copy-paste type of macro, but I can't even seem to get it to work, so if someone could help me that would be great. Please let me know if you have any further questions.
Trusted Members
December 20, 2019
Offline
From what you have wrtitten the process seems fairly straight forward, but the different sheets and locations make it very complex without seeing it. I know your work book is large but can you please put together a test file of say 5 lines in the same format?
I will be happy to try and help but guessing & recreating formats and locations/positions will be a waste of everyones time and is likley to lead to mistakes.
Purfleet
June 1, 2020
Offline
As you can see on Sheet1, you can tag each Project as "Yes" in column A. I will provide an example procedure that we can both follow to help give you an understanding.
So...
Lets tag Projects 402 and 404 as "Yes" in Sheet1. When you go to Sheet2, you can see that both of these are tagged "Yes". On Sheet 2, you can also see the Shipping # which is Cell B1 (value of 7204), and the Shipment Date which is Cell C1 (whatever date you open this.. for example's sake I will say 7/7).
Once this process is done and clarified, I want to have a command button (which will be on Sheet2) with your proposed macro assigned to it. Once you press the button on Sheet2, I want projects 402 and 404 to have the Shipping # 7204 (from Sheet2) on its designated cells (C2 & C4 - on Sheet1) and Shipment date on its designated cells (D2 & D4 - on Sheet1).
Now that the data has been filled out for projects 402 and 404, it is time for me to document another order which will involve other projects. This means I now have to un-tag the "Yes" from projects 402 and 404. Once untagged, I still want the data to stay.
NOTE: ALL projects are unique so there should be no issues on overriding in case you had any questions on that.
NOTE: Let's say this is for the first order shipment. Is there a way that you can design the code to say "if there is already data for the Shipping # and Shipment Date of the first shipment, then this will put the data on the second shipment" (which will just be the same thing but a couple columns down - everything of the same format).... and so on for multiple shipments
I am sorry if I typed too much, I am just trying to elaborate as much as possible. I have attached the sample workbook.
Trusted Members
December 20, 2019
Offline
Sorry work keeps getting in the way of more important stuff
Have a look at the attached and see what you think, its a bit rough and ready but i think it does what you want - i have also made it clear yes's from column A once run.
Note that i have changed the sheet names just so i knew what i was doing, if you channge them to anything else you will need to update the code
Sub UpdateRecords()
Dim ShipNo As String
Dim ShipDate As Date
Dim ProjectNo As String
Dim ProjectRange As Range
Dim ProjectCell As Range
Dim ProjectFound As Long
ShipNo = Range("b1")
ShipDate = Range("c1")
'Sets the range of project numbers to look up
Set ProjectRange = Range("d3:d" & Cells(Rows.Count, 4).End(xlUp).Row)
'Looks at each cell that is not blank and updates the records on the Log sheet
For Each ProjectCell In ProjectRange
If ProjectCell.Value = "" Then
GoTo Skip
Else
ProjectFound = Worksheets("Log").Range("b:B").Find(what:=ProjectCell).Row
Worksheets("Log").Range("c" & ProjectFound) = ShipNo
Worksheets("Log").Range("d" & ProjectFound) = ShipDate
End If
Skip:
Next ProjectCell
'clears yes's from the Log sheet once updated
Worksheets("Log").Range("a:a").ClearContents
End Sub
June 1, 2020
Offline
Thank you. I tested out the code and it works well. I am learning as I am writing these codes, so could you please provide clarification for these three lines?
Set ProjectRange = Range("d3:d" & Cells(Rows.Count, 4).End(xlUp).Row)
ProjectFound = Worksheets("Log").Range("b:B").Find(what:=ProjectCell).Row
Worksheets("Log").Range("a:a").ClearContents
I have highlighted what I do not understand in red. How should I interpret those ranges as well as the "Rows.Count,4"? What do these mean?
1 Guest(s)
