Cutting and Pasting using a Command Button

[thegreatcorn69]

Hi There,

I have read a couple of forums on this but am still not getting it right. Lets say I have 2 worksheets. Sheet 1 and Sheet 2.

I would like to have my command button do the following:

1. Cut the selected cell from my existing worksheet (this I have achieved albeit through assistance and not sure if it is correct)
2. Select Sheet 2
3. Find the next available column in Sheet 2
4. Paste the cut field in the sheet
5. As an added bonus I would like to be able to copy the text in another field in Sheet 1 to another adjacent field in Sheet 2.

Here is the code I have so far :

Private Sub CommandButton1_Click()


Dim a As Worksheet
Dim b As Worksheet
Set a = Sheets("Main Pipeline")
Set b = Sheets("Past")
a.Select
Selection.Cut
b.Select

b.Range("A1").End(xlUp).Offset(1, 0).Value = a.Range("a1").Value
End Sub

This code cuts the field but then does not insert it automatically into the next available column in my second sheet. Also - this does obviously not address point 5

Can someone help?

 

[tonyyy]

thegreatcorn69,

Welcome to the board.

The posted code doesn't quite match what you've stated in items 1 to 5, so the following is a guess at what you're trying to achieve...

Private Sub CommandButton1_Click()
Dim a As Worksheet, b As Worksheet
Dim rng As Range
Set a = Sheets("Main Pipeline")
Set b = Sheets("Past")

Set rng = Application.InputBox(prompt:="Please select a range.", Type:=8)
Application.ScreenUpdating = False
rng.Cut
b.Paste Destination:=b.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 'Pastes into the next blank row of Column A
End Sub

Cheers,

tonyyy

 

[thegreatcorn69]

thegreatcorn69,

Welcome to the board.

The posted code doesn't quite match what you've stated in items 1 to 5, so the following is a guess at what you're trying to achieve...

Private Sub CommandButton1_Click()
Dim a As Worksheet, b As Worksheet
Dim rng As Range
Set a = Sheets("Main Pipeline")
Set b = Sheets("Past")

Set rng = Application.InputBox(prompt:="Please select a range.", Type:=8)
Application.ScreenUpdating = False
rng.Cut
b.Paste Destination:=b.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 'Pastes into the next blank row of Column A
End Sub

Cheers,

tonyyy

Hi Tony,

Thanks for this. This is exactly what I needed!!

 

[tonyyy]

You're very welcome...