Copy any cell to a specific cell

[wmfaria]

Hello everybody!

I am trying to create a vba button that would copy any cell that the user has clicked and paste it to a specific cell..


if the user has the B5 cell clicked, the button would copy the contents of B5 to J10.
if the user has the A8 cell clicked, the button would copy the contents of A8 to J10.

I need the button to recognize the cell active and copy the contents, no matter what cell it is..


If anyone can help me it would be awesome!! Thank you so much guys!

[daffodil11]

Please Login or Register  to view this content.

[wmfaria]

Hey daffodil11!!
Thank you so much for the reply! It worked nicely.
I have no experience with vba, so, sorry for the silly question, but I did not find this anywhere. Just explanations on how to copy many values etc..

Thanks again!

[daffodil11]

No problem. It was just a few years ago I began my timid adventure in teaching myself.

You can Paste via same line as Copy, such as

Please Login or Register  to view this content.

Or you can do it explicitly with PasteSpecial

Please Login or Register  to view this content.

You could even simply set the destination value as the current cell

Please Login or Register  to view this content.

When in doubt, bring up the library in VB Editor with F2, and just search the term. Once you find the list of properties (adjectives) and methods (verbs) of that object, pick the one you think you need to use and hit F1 to see its proper methodology.

[wmfaria]

Wow, These are precious information for beginners! Awesome tips!
Again, Thank you very much daffodil11 for the availability and attention in answering.

I will follow what you mentioned and use the F2 and F1 keys... really nice stuff!

[wmfaria]

Would you mind if I ask you to explain me a little further how to use the 'Set the destination value as the current cell'....
Like... Is it possible to make Excel update my J10 cell with any active cell value without clicking any button?
Updates at every click on a cell.. Direct on the spreadsheet...

And is it possible to offset the active cell? Like.. If the user clicked on E1 vba sets the value of J10 with the value inside C1.

Sorry about my english.. was it possible to understand?

Thanks Again! So much!! I found some books about VBA in Excel that I will be starting studying, but I really need to finish this analysis I am doing..

[daffodil11]

What is the constant relation of the offset?

Like, Click A1, then 3 rows down, 4 columns right?

Are there a number of specific cells you want this to only happen to, like only when E1:Z1 are clicked, but no where else?

[wmfaria]

Hi daffodil11,

It would be limited between E2:E2746, and it would copy the value of 2 columns to the left...

If E2 is clicked, C2 is copied. If E300 is clicked, C300 is copied...

I think it is better to take a look at the spreadsheed... so you can understand better...

https://drive.google.com/open?id=0B8...m5NZmRUVE0wYVE

The 'mapunit' sheet has a list of unique 'mukeys' and other values that comes from the second sheet 'Component' (This is the raw data).

The 'component' sheet has various components for equal 'mukeys'. One 'mukey' can have 6 components, but on the 'mapunit' sheet, 'mukey' has to have one.

I created a 'LookUP' sheet that searches a 'mukey' and lists all components.. this list appear on the drop down of the 'mapunit' sheet.

I used your code to create the long button on the column D in 'mapunit' that copy the 'mukey' you select to the 'LookUP' and updates the list.

But all drop downs will have the same list, until you click a different mukey and click the button.

I would like the person to click on the cell E2 that contains a dropdown, and excel automatically send the 'mukey' on C2 value to the E1 on the 'LookUP' sheet.

So the array is updated.

This is my idea... =D

Thank you so much for helping me!

[daffodil11]

I'm sorry, but my company's firewall prevents access to the Google drive.

Do you have a small sample you can attach here?


It sounds like you need an Event macro, which is code that can run on a predefined trigger. Excel comes with a number of them already setup, such as Worksheet_SelectionChange which runs every time you select a different cell. From there you just specify additional criteria, such as how many cells selected at a time and if only on certain cells.

[wmfaria]

I zipped the excel file.. It is a lot of data..

But what you've just told sounds very good.. Actions driven by events...

By looking the file you will get my idea, update a cell that drives a formula to populate an array with results, to be shown on a drop down list...

[daffodil11]

Going through it now.

Holy geology, Batman.

[daffodil11]

I was able to see what you're trying to do; very clever.

First, let's give your Data Validation a little fix:

=OFFSET(LookUP!$E$2,,,COUNTA(LookUP!$E$2:$E$18)-COUNTBLANK(LookUP!$E$2:$E$18))

This will create a dynamic length field, which will only have as many entries to pick from that are necessary.

The worksheet event code goes directly on the worksheet's module, which you can easily get to by right-clicking the Mapunit tab name and selecting View Code.

Please Login or Register  to view this content.

[wmfaria]

Yeahh... Geology rocks man!! hahaha

You definitely got the idea absolutely correct! That was exactly what I was looking for.. No words.. You helped me achieve an enormous fix in this spreadsheet.. I really appreciate the availability and time you spent helping me.. You helped me a lot with these improvements... Thanks for the patience too..

I will definitely have to learn and master these VBAs techniques.. so, Master, do you have any book recommendation for me to start my journey? I am ready.. Professionally this is a enormous advantage...

Welll, I will set this thread as 'case closed'.. haha

Again, thank you very much!