Forum

Code to move values...
 
Notifications
Clear all

Code to move values from 2 sheet to another(Not copy/paste)

6 Posts
2 Users
0 Reactions
71 Views
(@scoti261)
Posts: 5
Active Member
Topic starter
 
This is a tracker workbook which follows samples through a process using their sample ID tag. On the main “Nucomat-Dashboard” tab, the operator has the ability to import incoming sample ID’s in cells D4:D36. The operator can then “load” them to a unit which will process them by clicking the corresponding button(Unit 5, etc.). Once the operator is finished with the samples, we need a way to show that they are done and have been forwarded to one of several other departments(Manual, Herzog, etc.) To do this, they scan the ID’s into cells K4:K36 on the main sheet and then click the corresponding button depending on which department will be receiving the samples.

I need a code that will find the list of ID’s on “Nucomat-Dashboard” sheet in cells K4:K36 in the “Received” sheet and move them to the “Completed” sheet under the appropriate column depending on which button is pressed on the “Nucomat-Dashboard” sheet. The formatted ID is one column to the right of the ID that will need to be moved.

Currently, the code copies the list and pastes it in the next available cell under the correct column on the “Completed” sheet however I really need it to move them from the “Received” to “Completed” sheet to prevent the “Received” sheet from having outdated information.

Then anyone can locate a sample by clicking on the “Sample Finder Magic Machine” button which brings up a user form to fill out. This part works great so I just need a way to move the ID’s from one sheet to the other instead of copy and paste.

Files

 
Posted : 24/11/2020 7:16 pm
(@rhysand)
Posts: 80
Trusted Member
 

Hello,

I need more details to complete the macro

at this point I have already built a macro that searches all Worksheets ("Received") based on your Worksheets data ("Nucomat-Dashboard") in range ("K4: K36")



the results of my macro were:

range address: $G$46
2,350xxxxxxT0050201006111022020005FD#
range address: $G$49
5,350xxxxxxT0050201006111022020026#
range address: $G$50
6,350xxxxxxT0050201006111022020003#
range address: $G$51
7,350xxxxxxT0050201006111022020006#
range address: $G$52
8,350xxxxxxT0050201006111022020007#

 

I need to know:

are the IDs unique or can there be the same IDs?
in Worksheets ("Received") the same ID can be in more than one range or only in one (I defined my macro to find only the first match)...?


with all occurrences and correspondence to 100% (view images)

Capturar.JPGCapturar2.JPG 



once found the IDs is to copy (example of the IDs found by my macro) Range of columns ("G" & "H" & "I" & "J") or just Range of columns ("G" & "J" ), is that the column ranges ("H" & "I") are hidden ... copy that data to Worksheets ("Completed")? 
and after copying, is the data found in Worksheets ("Received") to be kept or deleted?


Miguel,
 
 
Posted : 25/11/2020 7:06 am
(@scoti261)
Posts: 5
Active Member
Topic starter
 

This is great! I very much appreciate your help. The ID's will de unique and only appearing once in the "Received" sheet. Once they are found in the "Received" sheet they should be deleted and the empty cells removed so the remaining data can move up. Does that make sense? I believe the code needs to remove both columns for each found ID since the left column has the original ID and the right column has the formatted version. I am looking forward to your response as I have been trying to find someone to help me on this for a very long time. Thank you so much

 
Posted : 25/11/2020 1:42 pm
(@rhysand)
Posts: 80
Trusted Member
 

Hello,

 

if the item searched in Worksheets ("Received"), 
is found in column ("E") the value of column ("B") and column ("E") should be copied, 
<< columns "C" & " D "does nothing >>, 
to which column in Worksheets (" Completed ")? 
then just delete the cells in the columns ("B" & "C" & "D" & "E") that had the data

 

and if the item searched in Worksheets ("Received"),
is found in column ("J") or ("O") or ("T"), is the same question, where does the data go? to which column in Worksheets (" Completed ")?

 

I apologize for asking, but there are colors in the columns of Worksheets ("Received") different from Worksheets (" Completed ") 
and the header names are different

 

Worksheets ("Received"): and not counting the hidden columns

Unit 5
Unit 6
Unit 7
Manual Digestion

 

Worksheets("Completed"): and not counting the hidden columns

Manual Time
Herzog Time
LECO Time
AXN Time
Line 5 Time
LIMS Co's Time

 

 

Miguel,

 
Posted : 25/11/2020 3:43 pm
(@scoti261)
Posts: 5
Active Member
Topic starter
 

You are correct about which columns the data gets deleted from. Once the user scans in the ID's they will choose which column the ID's get sent to on the "Completed" sheet by clicking one of the buttons above where they scanned in the ID's. For example if they send those samples to Manual then they will click the Manual button and the ID's will be pasted into the column with the name Manual on the "Completed" sheet. Does that make sense? That way when someone wants to search for a sample it will show where it was sent to. 

 
Posted : 25/11/2020 6:25 pm
(@rhysand)
Posts: 80
Trusted Member
 

Hello,

I added a new userform (UserForm2), it is with him that you will do the tests and check if this is what you want, you need to adapt part of your codes to my macro

I added two new modules, with several macros: (cod_for_findAllMatches) & (cod_for_range_cells)

you will check that there is a sheet with the name ("Received (8)"), is a copy of Worksheets ("Received"), 
before performing a test, you must copy the sheet, make tests, delete the test sheet, rename the copy with the name Worksheets ("Received")

do not execute the part of your code that cleans the cells in K4: K41 ... 
do the tests, and if everything goes as expected, then yes, add whatever you want to complement my macro

I deactivated your code: Public Sub Worksheet_Change(ByVal Target As Excel.Range)   in  Worksheets("Completed")

it crashes with my macro when copy & paste
I attached a file, there were a lot of codes and long codes to paste here


Miguel,
 
 
 
Posted : 26/11/2020 4:05 pm
Share: