VBS script to copy various cells from one sheet to sheet2 in new row

[Hereheis]

Hi all,

I'm trying to create a VBS script (noob here) that copy's various selected cells to sheet2 to the next available row. A basic example would be to select cells A1, C4 and D8 on sheet1 then copy to sheet2 to the next available row. Example the next available row is row 4 on sheet2 so the selected cells on sheet1 A1, C4 and C8 would copy to cells A4, B4 and C4 on sheet to in order. I found the below which was helpful but not quite what I need:

Copy Values to Next Empty Row

Sub CopyStuff()

Range("A1:J1").Copy

Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub

The above works perfectly if you data on sheet1 on is in a sequence eg. A1:A8 but as soon as you change the range to something like A1, C4, C8 it copy's each cell to a new row.

Please can anyone help?

Thank you in advance

 

[VBE313]

I added error handling so the user can only select 3 or less cells to copy to Sheet2

Sub Herehis()
Dim cellCount As Long, lastRow As Long, arr As Variant, cl As Object, x As Long, strCells As String
ReDim arr(0, 2)
x = -1
cellCount = Selection.Count
If cellCount > 3 Then
    MsgBox "Sorry you have too many selected"
    Exit Sub
End If
For Each cl In Selection
    x = x + 1
    arr(0, x) = cl.Value
Next cl
With Sheets("Sheet2")
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    strCells = "A" & lastRow & ":C" & lastRow
    .Range(strCells).Value = arr
End With
End Sub

 

[Hereheis]

I added error handling so the user can only select 3 or less cells to copy to Sheet2

Sub Herehis()
Dim cellCount As Long, lastRow As Long, arr As Variant, cl As Object, x As Long, strCells As String
ReDim arr(0, 2)
x = -1
cellCount = Selection.Count
If cellCount > 3 Then
    MsgBox "Sorry you have too many selected"
    Exit Sub
End If
For Each cl In Selection
    x = x + 1
    arr(0, x) = cl.Value
Next cl
With Sheets("Sheet2")
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    strCells = "A" & lastRow & ":C" & lastRow
    .Range(strCells).Value = arr
End With
End Sub

Thanks VBE313 appreciate your response. I'm not sure if my example was very clear but the real scenario I have is that I have around 45 specific cells on sheet1 that are not in any particular order. I'm creating a macro (from the VBS script) that copy's those selected cells from sheet1 to the next empty row on sheet2 in order. When I get back home I can test it. Where can I specify the specific cells to be copied in the script. Also I would like to then delete the cells so that the next day data is entered and I can do the same.

Really appreciate your help.

Kind regatds

 

[Hereheis]

Anyone able to help with this please?
Thanks

 

[VBE313]

Sub Herehis()
Dim cellCount As Long, lastRow As Long, arr As Variant, cl As Object, x As Long, strCells As String
ReDim arr(0, 44)
x = -1
cellCount = Selection.Count
If cellCount > 45 Then
    MsgBox "Sorry you have too many selected"
    Exit Sub
End If
For Each cl In Selection
    x = x + 1
    arr(0, x) = cl.Value
Next cl
With Sheets("Sheet2")
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    strCells = "A" & lastRow & ":AS" & lastRow
    .Range(strCells).Value = arr
End With
End Sub

 

[Hereheis]

Sub Herehis()
Dim cellCount As Long, lastRow As Long, arr As Variant, cl As Object, x As Long, strCells As String
ReDim arr(0, 44)
x = -1
cellCount = Selection.Count
If cellCount > 45 Then
    MsgBox "Sorry you have too many selected"
    Exit Sub
End If
For Each cl In Selection
    x = x + 1
    arr(0, x) = cl.Value
Next cl
With Sheets("Sheet2")
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    strCells = "A" & lastRow & ":AS" & lastRow
    .Range(strCells).Value = arr
End With
End Sub

Thanks where do I enter my specific cell locations buddy.

Kind regards

 

[VBE313]

Whatever you have selected is what goes to page 2. You just click them with your mouse while holding control buddy.

 

[Hereheis]

Whatever you have selected is what goes to page 2. You just click them with your mouse while holding control buddy.

 

[Hereheis]

Thanks VBE that looks very clever ? I need to be able to specify certain cells though eg A2, D5, F4 as examples not clickable ones?

 

[Hereheis]

Hey are you able to help with the below?

Kind regards