Copy new value to a different worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2603264%22%20slang%3D%22en-US%22%3ECopy%20new%20value%20to%20a%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2603264%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon%20Community%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20the%20following%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20file%20with%20contains%3C%2FP%3E%3CP%3ECode%3A%20Machine%20ID%3C%2FP%3E%3CP%3ELocation%3A%20Uses%20List%20data%20validation%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20location%20can%20change%20multiple%20times%20and%20I%20need%20to%20copy%20to%20a%20new%20worksheet%20the%20Code%23%20and%20new%20location%20each%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20updated%20a%20sample%20file%20for%20your%20information.%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53299%22%20target%3D%22_blank%22%3E%40Logaraj%20Sekar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2603264%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2603621%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20new%20value%20to%20a%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2603621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F919416%22%20target%3D%22_blank%22%3E%40MarioCadena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3ECould%20you%20please%20send%20us%20more%20detailed%20information%3F%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E%3CSTRONG%3EThe%20storage%20location%20can%20change%20several%20times%3C%2FSTRONG%3E%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E...%20when%2C%20from%20where%20to%20where%2C%20how%20many%20times%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E...%20is%20the%20storage%20location%20specified%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSTRONG%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3Eand%20I%20have%20to%20copy%20the%20code%20%23%20and%20the%20new%20location%20into%20a%20new%20worksheet%20every%20time.%3C%2FSPAN%3E%3C%2FSTRONG%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E...%20when%20(for%20which%20cell)%20does%20a%20new%20worksheet%20have%20to%20be%20copied%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E...%20at%20which%20cell%20does%20the%20data%20appear%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E...%20or%20will%20this%20manual%20be%20entered%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E*%20Knowing%20the%20Excel%20version%20and%20the%20version%20of%20the%20operating%20system%20would%20be%20an%20advantage%2C%20especially%20for%20you.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EThere%20are%20some%20questions%20arising%20from%20your%20text%20look%20at%20first%20suggest%20themselves.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2607724%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20new%20value%20to%20a%20different%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2607724%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40NikolinoDE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20below%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSTRONG%3EThe%20storage%20location%20can%20change%20several%20times%3C%2FSTRONG%3E%3CBR%20%2F%3EWhen%20I%20mention%20storage%20location%3B%20I%20referred%20to%20the%20location%20column%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3E.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3Eand%20I%20have%20to%20copy%20the%20code%20%23%20and%20the%20new%20location%20into%20a%20new%20worksheet%20every%20time.%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20have%20updated%20the%20file%20to%20make%20it%20clearer%20but%20basically.%3C%2FP%3E%3CP%3EI%20get%20a%20list%20with%20Code%20and%20Location%20Columns.%3C%2FP%3E%3CP%3ECode%20Column%20is%20an%20ID%23%20value.%3C%2FP%3E%3CP%3ELocation%20is%20the%20name%20of%20a%20place%2C%20usually%20blank.%20Then%20people%20would%20have%20access%20to%20the%20file%20and%20change%20the%20location%3B%20sometimes%20multiple%20times.%3C%2FP%3E%3CP%3EI%20need%20to%20copy%20any%20new%20location%20value%20in%20the%20%22Record%20Values%22%20tab%20including%20the%20Code%20Value.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good afternoon Community,

I have the following issue.

 

I have a file with contains

Code: Machine ID

Location: Uses List data validation

 

The location can change multiple times and I need to copy to a new worksheet the Code# and new location each time.

 

I have updated a sample file for your information.
@Logaraj Sekar 

7 Replies

@MarioCadena 

 

Could you please send us more detailed information?

 

The storage location can change several times

... when, from where to where, how many times?

... is the storage location specified?

. and I have to copy the code # and the new location into a new worksheet every time.

... when (for which cell) does a new worksheet have to be copied?

... at which cell does the data appear?

... or will this manual be entered?

 

* Knowing the Excel version and the version of the operating system would be an advantage, especially for you.

 

There are some questions arising from your text look at first suggest themselves.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

 

 

Thank you, @NikolinoDE 

Please find below


The storage location can change several times
When I mention storage location; I referred to the location column value.

 

. and I have to copy the code # and the new location into a new worksheet every time.

I have updated the file to make it clearer but basically.

I get a list with Code and Location Columns.

Code Column is an ID# value.

Location is the name of a place, usually blank. Then people would have access to the file and change the location; sometimes multiple times.

I need to copy any new location value in the "Record Values" tab including the Code Value.

@MarioCadena 

 

I fail to see the logic of you copying the Code# and Location to a new worksheet.

This new worksheet is it just a blank worksheet?

or there are other fields in this new worksheet where you paste the Code# and New Location?

if it is just a New Blank Worksheet, then that would be inefficient because you're already getting the information complete, so I see no value in you copying it into a New Blank Worksheet. This is why Mr NikolinoDE is somewhat hesitant to commit to helping because your narrative has quite big holes in it and you're not  narrating the whole situation, where it comes from is absolute you get the Code# and Location name (2 columns), but you don't exactly show what this New Worksheet looks like (so we will assume) it is just a New Blank Worksheet (if it isn't so then that proves the ambiguity your narrative is communicating to the problem solvers).

 

Moreover, the problem solvers does not understand why you have to copy and paste information that you already have, instead of manipulating the data by adding a date and a time of when you received the data so you can populate somewhere else or just create a pivot table iterating the whereabouts of the Machine at a certain point in time.

 

cheers

Thank you for your message. Sorry for the confusion.
There is a query that updates a table in excel based on machines in transit; when the file is opened the query refreshes and any selection of locations will be deleted. That is the reason we want to copy any change of the location column to be added to a new worksheet. So, it is permanent.
I hope this helps.

@MarioCadena 

 

I am assuming this query is Power Query in excel?

How often does the file get opened? Every Hour? 2 Hours? 4 Hours? You left that seemingly unimportant/irrelevant part of the narrative

Instead of copying, why not saving the query as a csv file, which records when the data was acquired (Date and Time) in a designated folder.  When doing a query by folder, all data saved in that folder are combined and can be manipulated by date/time into a pivot table which will list the machine and the its whereabouts location wise will be iterated in the pivot table. Don't forget to rename it though or probably save the query as a template, where it will update the query and at the same time change the filename to "FileName01", 02, 03... and so on and so forth

 

cheers

Thank you for your message.

The number of machines in transit varies daily (there is daily arrival and new orders); therefore, the query reads directly from the server of our supplier.
Access to the file can be a few times per day.

@MarioCadena 

 

So the necessary updating of the file is once in the AM and once at the end of business day, Any other access is incremental other things so maybe once in the morning once at midday and once at end of business day and everytime it is opened to update, save a csv copy to a designated folder much like an update log, any other types of access should be a Power query against the File query that is connected directly to the supplier server. your thoughts?

 

cheers