Copying information from selected cells with VBA

wtktrichards

New Member
Joined
Jun 10, 2002
Messages
48
Hope someone can help (if it is possible). Here’s the scenario...A user will highlight 3 separate cells on a worksheet by holding the Ctrl key. The script would then need to copy the information from these cells and paste them into selected cells on a different worksheet.
Would appreciate any help on this.
Thanks
Will
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This should get you started:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim c As Range
    If Target.Areas.Count <> 3 Then Exit Sub
    For Each c In Target.Areas
'   *** Replace the next line with your code ***
        MsgBox c.Address
    Next c
End Sub
 

wtktrichards

New Member
Joined
Jun 10, 2002
Messages
48
I think I understand what you have written (still on the first step of the vba ladder) so would I put something like..
Range("a1").Select
but how would i tell it to copy in the information, and move to the next cell to the right, so that the for-next loop can carry on.
Thanks
 

wtktrichards

New Member
Joined
Jun 10, 2002
Messages
48

ADVERTISEMENT

Just to another worksheet, cell A13.
Thanks
Will
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you copy each of the selected cells to cell A13 on another worksheet, only the last copy will take effect. So A13 and ??? and ???

Is there a relationship between the selected cells and the target cells?
 

wtktrichards

New Member
Joined
Jun 10, 2002
Messages
48

ADVERTISEMENT

Basically our users fill in a training diary in excel. I would like them to be able to press a button (after they have highlighted 3 cells with the date, time and session)this would then be pasted directly on to a separate worksheet that is set out like a letter(which can be printed out) The cells that the information needs to go in to are a7,b7,c7
Thanks
Will
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Areas.Count <> 3 Then Exit Sub
    Dim Answer
    Dim ToRng As Range
    Dim c As Range
    Dim x As Integer
'   *** Change sheet name to suit ***
    Answer = MsgBox("Copy to Sheet2?", vbQuestion + vbYesNo, "Training Diary")
    If Answer = vbYes Then
        Set ToRng = Worksheets("Sheet2").Range("A7:C7")
        For Each c In Target.Areas
            x = x + 1
            ToRng.Cells(1, x) = c
        Next c
    End If
End Sub
 

Forum statistics

Threads
1,143,921
Messages
5,721,549
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top