• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Code to move values from 2 sheet to another(Not copy/paste)|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Code to move values from 2 sheet to another(Not copy/paste)|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosCode to move values from 2 sheet to…
sp_PrintTopic sp_TopicIcon
Code to move values from 2 sheet to another(Not copy/paste)
Avatar
Scoti Montierth

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
November 24, 2020
sp_UserOfflineSmall Offline
1
November 24, 2020 - 9:16 am
sp_Permalink sp_Print
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

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
2
November 24, 2020 - 9:06 pm
sp_Permalink sp_Print sp_EditHistory

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.JPGImage Enlarger
Capturar2.JPGImage Enlarger
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,
 
sp_PlupAttachments Attachments
  • sp_PlupImage Capturar.JPG (113 KB)
  • sp_PlupImage Capturar2.JPG (110 KB)
Avatar
Scoti Montierth

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
November 24, 2020
sp_UserOfflineSmall Offline
3
November 25, 2020 - 3:42 am
sp_Permalink sp_Print

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

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
4
November 25, 2020 - 5:43 am
sp_Permalink sp_Print

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,

Avatar
Scoti Montierth

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
November 24, 2020
sp_UserOfflineSmall Offline
5
November 25, 2020 - 8:25 am
sp_Permalink sp_Print

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. 

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
6
November 26, 2020 - 6:05 am
sp_Permalink sp_Print sp_EditHistory

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,
 
 
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Shanna Henseler, Lawrence Smith
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.