Code

[Alex]

Dear ALL

I have the following question:

I placed the button on the excel sheet and assign a macro which cuts the
cells' values from range b1:b4 and pastes them into range a10:d10 everything
is ok and works perfectly, but how can I do so that when I press the button
again the new values which i type in range b1:b4 placed in a11:d11 ? and so
on, is it possible?

Please help me with this
Thank you in advance

My Code:
Private Sub CommandButton1_Click()
If Range("A10") = "" Then

Range("B1").Select
Selection.Cut
Range("A10").Select
ActiveSheet.Paste
Range("B2").Select
Selection.Cut
Range("B10").Select
ActiveSheet.Paste
Range("B3").Select
Selection.Cut
Range("C10").Select
ActiveSheet.Paste
Range("B4").Select
Selection.Cut
Range("D10").Select
ActiveSheet.Paste
Else
Range("B1").Select
Selection.Cut
Range("A11").Select
ActiveSheet.Paste
Range("B2").Select
Selection.Cut
Range("B11").Select
ActiveSheet.Paste
Range("B3").Select
Selection.Cut
Range("C11").Select
ActiveSheet.Paste
Range("B4").Select
Selection.Cut
Range("D11").Select
ActiveSheet.Paste

End If

End Sub

what should I do to continue the code for next ranges?

 

[Rowan]

Try

Private Sub CommandButton1_Click()

Dim rowNum As Long

If Range("A10").Value <> Empty Then
rowNum = Cells(Rows.Count, 1).End(xlUp).Row + 1
Else
rowNum = 10
End If

Range("B1").Cut Destination:=Cells(rowNum, 1)
Range("B2").Cut Destination:=Cells(rowNum, 2)
Range("B3").Cut Destination:=Cells(rowNum, 3)
Range("B4").Cut Destination:=Cells(rowNum, 4)

End Sub

Hope this helps
Rowan

 

[Alex]

WOW Thank you Rowan
It Works Perfectly It's hard for me to understand some parts of code but
Thank you again

 

[Norman Jones]

Hi Alex,

Try:

Private Sub CommandButton1_Click()
Dim rng As Range

If Application.CountA(Me.Range("A1010")) = 0 Then
Set rng = Me.Range("A10")
Else
Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
End If

Me.Range("B1:B4").Copy
rng.PasteSpecial Paste:=xlAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True

End Sub

 

[Greg Wilson]

Try:

Private Sub CommandButton1_Click()
Dim rng1 As Range, rng2 As Range
Dim rw As Long
rw = Cells(Rows.Count, 1).End(xlUp).Row + 1
rw = Application.Max(rw, 10)
Set rng1 = Range("B1:B4")
Set rng2 = Range(Cells(rw, 1), Cells(rw, 4))
rng2.Value = Application.Transpose(rng1.Value)
rng1.ClearContents
End Sub

Regards,
Greg

 

[Rowan]

You're welcome. Quick (and incomplete) explanation.

You don't need to select cells to carry out operations on them. So your code:

can be rewritten as:

You can refer to a range (cell) using the Cells property so which consists
of a row reference followed by a column reference i.e. Cells(row,column). So

Range("A10") becomes Cells(10,1) giving you:

Rather than actually stating the row number I have used a variable called
rowNum. If Cell A10 is not empty then rowNum is set by starting at the last
row in column A in your version of Excel (rows.count,1) and then moving up to
the first populated cell (End(xlUp).Row) and adding 1.
If A10 is empty then rowNum is set to the value 10.

I hope this has made some sense and not left you even more confused

Rowan

 

[Alex]

Rowan your explanation was really usefull want to thank you for that.

Here I also want to extend my appreciation to Norman Jones, his version is
also good because:

In Rowan version if I have some datas in the lower fields the cutted cells
will go under the datas, as to Norman's one My Range is going down wheter I
have anything or not

Once Again Thank you all

I Love this Community and Love you all.

 

[Norman Jones]

Hi Alex,

Here I also want to extend my appreciation to Norman Jones, his version is
also good because:

Thank you.

However, given a choice, I would go with Greg's more elegant code.