Copy & Paste Cell value into another sheet based on selection not fixed range - Macro/vba.

[msussams]

Hi All,
I am having an issue with my VBA/Macro
I'm trying to copy 5 cell contents to a different sheet
I've managed to input data to a fixed cell range and it'll insert it at the bottom of data
but I need to do it based on selected cells.

so basically 09:15;J Jack Bob; H23456; DUU and YY should be copied and pasted into this:

Sub CopyName()
Worksheets("JohnSmith").Range("A" & Rows.Count).End(xlUp).Offset(0, 0) = Worksheets("Diary").Range(ActiveCell.Offset(0, -1)).Value   'date
Worksheets("JohnSmith").Range("B" & Rows.Count).End(xlUp).Offset(1, 1) = Worksheets("Diary").Range(ActiveCell.Offset(0, 0)).Value   'name
Worksheets("JohnSmith").Range("C" & Rows.Count).End(xlUp).Offset(1, 1) = Worksheets("Diary").Range(ActiveCell.Offset(1, 0)).Value
Worksheets("JohnSmith").Range("D" & Rows.Count).End(xlUp).Offset(1, 1) = Worksheets("Diary").Range(ActiveCell.Offset(2, 0)).Value
Worksheets("JohnSmith").Range("E" & Rows.Count).End(xlUp).Offset(1, 1) = Worksheets("Diary").Range(ActiveCell.Offset(3, 0)).Value
Call selectandformat
End Sub

This is what i'm trying to do - but it doesn't work
Does anyone have any tips/pointers to show where i am going wrong?

Many thanks in advance

 

[Osvaldo Palmeiro]

... input data to a fixed cell range
I've considered this range as C2:C5 of sheet Diary.

Please, see if the code below could be a start point.

Sub CopyName()
  Sheets("Diary").[C2:C5].Copy
  Sheets("JohnSmith").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues, Transpose:=True
  Sheets("JohnSmith").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Date + Sheets("Diary").[B2]
'Call selectandformat
End Sub