Macro to move data from sheet1 to sheet2 when clicked

**excel77** · 07-13-2009, 09:36 AM

Please i have attached a test spreadsheet for what i really need.

i want that when the any cell in column B is click, the macro will check if the data is BOLD and then paste it in the second sheet cell b7, else it should ignore the cell.

thanks.

**JBeaucaire** · 07-13-2009, 10:55 AM

1) Right-click on TOTALPLANT sheet tab and select VIEW CODE.
2) In the window that appears, paste in this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Font.Bold = True Then _
        Target.Copy Sheets("UNIT").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub

3) Press Alt-Q to close the VB window.
4) Save your sheet.

Now anytime you select a BOLD cell on that sheet, it will be copied to the next empty cell in column B on the UNIT page.

If you really just want the last bold value selected to always be copied to cell B7 (same cell over and over), then use this code instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Font.Bold = True Then _
        Target.Copy Sheets("UNIT").Range("B7")
End Sub

CAVEATS:
- Using sheet-event codes like this will cost you your UNDO command on that sheet.
- This will require you to enable macros when opening your sheet.

**excel77** · 07-14-2009, 04:22 AM

THANKS! IT WORKS!

I want to adapt this to a problem i posted earlier, how can i progress with it?
the attached worksheet has staff names in column B, when names that at bold are selected, the associated info are transfered. for exampe course titles to the next worksheet.

the issues is how to make the macro know the range of cell to copy e.g if Emeka Lioegbuam is selected from b5, then e5 to e10 to be copied to sheet1 b11 to b15. if i get this code i will expand for the other cells.

**JBeaucaire** · 07-14-2009, 01:18 PM

Here's a way. We're going to use the trick above to simply give the cell with people's names in it the name "NAME". Then we use that name in the formulas on Sheet1 to draw over the values in relation to their position from the cell currently named "NAME".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 And _
    Target.Row > 4 And _
        Target.Font.Bold = True _
            Then Target.Name = "Name"
End Sub

You will have to clean up your value a little. To give the sheet some sense of order, it has to stop listing values at some point or it flows into the next name classes. To stop that from happening, we're evaluating some of the results and once we get 0 we stop.

That means cells like S40 are a problem and should be moved up to eliminate blank rows inside a single person's lists of classes. If you don't move S40 up to S39, you'll never see it on Sheet1.