How to copy any cell value by just clicking on it then press the macro button it will paste the value in another cell?

0
votes

Here are the details: There are two sheets in an Excel macro-enabled file. In sheet-1, I have a column of all unique values. I want to click on any of those cells and then press the macro button which says "Filter", it will copy the value of the clicked cell and go to sheet-2 and paste that value in the filter search in a particular column. That's it. I tried recording the macro, thinking it will understand my input actions like click to select the cell, ctrl+c, ctrl+v. It is actually understanding those actions but it is copying and searching only the cell value that I have clicked while recording the macro. So when I click on another cell and then the macro button, it is searching for that only value that I clicked during the recording. I want it to be more dynamic. Like if I click on any cell, it should copy that particular value in paste it in the filter search in sheet-2.

1
excelvba
What have you tried? Where are you stuck? Have a look at Private Sub Worksheet_SelectionChange(ByVal Target As Range) – Qualia Communications
The minimal amount of code would be something like: Range("A1") = Selection and change that range to the "particular cell". This implies ActiveSheet.Range("A1").value = Selection.value – Christofer Weber
@Qualia, Please take a look at the description. I have elaborated the issue to give more clarity. – Himanshu Raul
@ChristoferWeber Please take a look at the description now. I have elaborated the issue to give more clarity. – Himanshu Raul

1 Answers

1
votes

Do you want to use the value as an autofilter?
If so, I guess you should have a row like the following in your recorded macro:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= "something"

Remove everything else from this recording, and change the row to:

Sheets(2).ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=Selection.Value

But using your table name and the correct sheet name or index, and field.
The point is, change the value after Criteria1:= to Selection.Value and refer to a specific sheet, not ActiveSheet.

enter image description here

Mind you that this is a minimal effort approach. It doesn't handle multiple selections. I've found that if there is a range larger than one cell, it picks the "last" cell as the filter value.