Moving data from one part of a worksheet to another with a button

  • Thread starter Thread starter kyle.murdoch
  • Start date Start date
K

kyle.murdoch

I'd like to move a block of cells, say A1:B1 for simplicity to another
area (Say, C16:D16) with a button (macro).

Say I do this multiple times though, instead of having A1:B1 overwrite
the same cells, I'd want it to move to the next row down (C17:D17) and
move the data there.

Is this possible in an easy to program answer? I have VB programming
experience but NO experience with Excel, and whenever I look at excel
code, it's all gobblygook to me!
 
Hi Kyle,

Try:
'===============>>
Private Sub CommandButton1_Click()
Dim rng As Range
Dim sRng As Range
Dim dRng As Range

Set rng = Range("C16")
Set sRng = Range("A1:B1")
Set dRng = Cells(Rows.Count, "C").End(xlUp)(2)

sRng.Copy Destination:=IIf(IsEmpty(rng), rng, dRng)

End Sub
'<<===============
 
i used a different approach - using a named range to store the destination
cell address. this way makes no assumptipons as to what else might or might
not be on the worksheet. also allows for easily moving 1 or multiple cells
of data.

- voodooJoe

Private Sub CommandButton1_Click()
'you need to first name the original destination cell, it is 'joe' in this
example
'also assumes the values you want to move are selected

With ThisWorkbook.Names("joe")
Set tmp = .RefersToRange.Offset(Selection.Rows.Count)
Selection.Cut .RefersToRange
.RefersTo = tmp
End With
Set tmp = Nothing

End Sub
 
Back
Top