Copying information from selected cells with VBA

[wtktrichards]

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

 

[Andrew Poulsom]

This should get you started:

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]

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

 

[Andrew Poulsom]

Where do you want to copy the data to?

 

[wtktrichards]

Just to another worksheet, cell A13.
Thanks
Will

 

[Andrew Poulsom]

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]

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]

Try this:

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

 

[wtktrichards]

Thanks very much i will give that a go.