Macro Help

[Anthony Warburton]

I have a macro with the following script:-

Option Explicit
Sub Deposit()
Dim fromWks As Worksheet
Dim toWks As Worksheet
Dim toCell As Range

Set fromWks = ActiveSheet

Set toWks = Worksheets("Deposits")

With toWks
Set toCell = .Cells(.Rows.Count, "C").End
(xlUp).Offset(2, 0)
End With

With fromWks
toCell.Value = .Range("H3").Value
toCell.Offset(0, 2).Value = .Range("D12").Value
toCell.Offset(0, 4).Value = .Range("D14").Value


End With

End Sub



The above will paste certain selected cells into
my 'deposits' worksheet which is great, but is there a way
to actually get it to select a range of cells paste them
into 'deposits' worksheet then select another set of cells
and past them on the next line in the 'deposits' worksheet
in ONE CLICK ??

 

[Dave Peterson]

In a private exchange:

This version uses Copy|paste, but you could still use the assignments of values
if you wanted:

Option Explicit
Sub Deposit()
Dim fromWks As Worksheet
Dim toWks As Worksheet
Dim toCell As Range
Dim rng1 As Range
Dim rng2 As Range


Set fromWks = ActiveSheet

Set toWks = Worksheets("Deposits")

With toWks
Set toCell = .Cells(.Rows.Count, "C").End(xlUp).Offset(2, 0)
End With

With fromWks
Set rng1 = .Range("a1:b19")
Set rng2 = .Range("c13:e99")

rng1.Copy _
Destination:=toCell

'adjust tocell

Set toCell = toCell.Offset(rng1.Rows.Count)

rng2.Copy _
Destination:=toCell

End With

End Sub