View original (French)

Excel - Copy the content of the active cell

Solved
Noibeu -  
 anonymike -
Hello,

I am a beginner with Excel and after a few days of research (rest assured, not full-time), I have decided to present my problem to you...

I need to display the content of the active cell in another defined cell. That is, no matter where I click on my Excel sheet, the content of that selected cell should appear in my other cell.

I have tried using the function CELL("contents",K12) but it only gives me the content of cell K12.

In the CELL function, is it possible to replace K12 with something that refers to the active cell?

If not, is there a function that could solve my problem?

If not, I imagine it is possible with a macro, but since I know nothing about it, if it is possible to have the code...

Thank you in advance...

Noibeu
Configuration: Windows XP Internet Explorer 6.0

2 answers

  1.
    ptidavid Posted messages 1322 Status Member 108
     
    Here’s a solution, step by step. In Excel:
    - click on the Tools menu, then on Macro and in the list that appears, click on "Visual Basic Editor",
    - in the VBAProject project explorer on the left side of the screen, you have your different sheets in your Excel workbook. For example, let's take the sheet titled Sheet1 under Microsoft Excel Objects. Double-click on "Sheet1 (Sheet1)"
    - a blank code page then opens in front of you. Right at the top of this page, you will see marked in a drop-down list "General". Click on this and choose "Worksheet" instead. The drop-down list on the right will then update: open it and choose "SelectionChange". SelectionChange is actually a macro that runs every time you change the active cell in your Excel sheet. You just need to integrate the code to copy the content of the active cell into each new cell you click on.
    - in the new page, you have the following content:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    End Sub

    Private Sub etc. is the header/name/beginning of your macro and End Sub indicates the end of it. So all the content of your macro is to be written between this header and the End Sub.

    - to reach the cell copy instruction, the active cell is referred to as ActiveCell in VBA Excel. The value contained in this cell is called ActiveCell.Value. Any cell in Excel is written as Cells(row,column), its value being Cells(row,column).Value.
    So, for example, if you want to copy the content from cells A1 and B1 into a cell that you clicked on (which then becomes active), here is the instruction to insert in the body of the macro:
    ActiveCell.Value = Cells(1, 1).Value

    In the end, your macro will look like this:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveCell.Value = Cells(1, 1).Value
    End Sub

    Then save the macro, test it by writing something into cell A1 and B1, validating, and then clicking anywhere else on the sheet. Then I will let you adapt the cell you want to copy ;-)

    There you go, I hope I helped you. If this is not exactly what you were looking for, feel free to correct me.
    12
    1. Noibeu
       
      Thank you very much Ptidavid,

      it works perfectly!! There was just a small issue, which is that with what you gave me, the content of cell A1 went into the active cell. I wanted the opposite (for the content of the active cell to go into A1).

      But since you were very clear and precise in your explanation, it wasn't hard to understand that instead of ActiveCell.Value = Cells(1, 1).Value, it should be Cells(1, 1).Value = ActiveCell.Value.

      Thank you again for taking the time to respond to me and for the clarity of your explanations!!


      Noibeu
      0
    2. ptidavid Posted messages 1322 Status Member 108 > Noibeu
       
      You're welcome ;-) Don't forget to check "Resolved" above your first post!
      0
    3. Helveticus
       
      There are people who know how to explain and take the time to do it... Bravo and thank you very much.
      0
    4. PourAprendre
       
      ForLearning: Congratulations to PtiDavid because everything is perfectly clear just by reading.
      Thank you and this gift must be nurtured and duplicated.
      0
    5. touroul Posted messages 520 Registration date   Status Member Last intervention   16
       
      Even years later, it still serves its purpose.
      Thank you!
      1
  2. touroul Posted messages 520 Registration date   Status Member Last intervention   16
     
    Hello community.

    Sorry to bring up this somewhat dusty subject that is still relevant.

    In the same vein, I would like to create a simple copy of the ActiveCell to the clipboard (to paste it elsewhere).
    Additionally, I would like to limit this function to cells B3:B20 on the "Soudure" and "Collage" sheets.

    Here's where I am:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ("B3:B20")
    Then Range.ActiveCell.Value.Select
    Selection.Copy
    End Sub

    Can I ask for your help?

    Thanks in advance!

    ------------Subject moved to new post -------
    0