Command button: copy and paste in next available cell on another worksheet

[Saintsation]

Hi all,

New here and very new to the vba world. I have searched for an answer to my question, but can not come across it.

Just wondering if this is possible....create a command that will copy the data I have selected and paste it to another worksheet. Also, I need the copied data to be placed in the next blank row in the other worksheet. Now here is where I can not find my answer. I need the "selected" data to change. Example: Rows 5-10 and Columns A-F are full, and I select Row 3 with Columns A-F and press the "command button." I would like it to copy what I selected and paste it in another worksheet. Also, I need the copied data to find the next available row in a range. Meaning, it can only paste in Rows 10-40 and Columns A-F.

I would greatly appreciate any advice on this matter. Thanks!

[ads_3131]

The code below will work on a button to copy the row from one sheet and paste to the next available row on another .... this code is from one of my previous spreadsheets.....

also see attached example...


Sub Macro21()

' This code is specific to a button to copy a set range from one cell to another then to clear the row copied
' Macro recorded 03/08/2011 by Adam Sargeant

Range("G124:U127").Copy

Call Archive

Sheets("GuardShop").Select
Range("F124:L127,M125:N126,P124:S127,T124:IJ126").ClearContents

End sub
--------------------------------
Sub Archive()

' Macro recorded 03/08/2011 by Adam Sargeant

Sheets("Archive").Select
Range("C" & Cells.Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste

end sub

[Saintsation]

Thanks ads, I appreciate your time!

I reviewed your example and I think I did not explain my self correctly. Sorry :(

Is it possible to not have the range pre-determined. Meaning, in the "first" worksheet I will select a row through a certain column randomly and press the "command button" to send the data that I selected/highlighted to a certain range in the "second" worksheet? Also, I will have different categories on the "second" worksheet. For example: In "first" worksheet I will have 4 buttons. The button named "Send to A" will send the data I selected to the "second" worksheet in the range of Rows 4-14 and Columns A-F. The second button named "send to B" will send the data to Rows 16-26 and Columns A-F...and so forth. When sending the data I need it to find the first available row in that range.

Not sure if this is possible :dunno

Thanks again for your help!

[Saintsation]

:banghead:

I am having so much trouble, to what seems to be a simple task. This is the code (below) that I thought would work, but it does not seems to run properly. Not sure how to fix it. As I stated previous, I am trying to cut data from a row if a cell value = "DONE" then pasted to another worksheet in the next available row. I would greatly appreciate any help with this...or just a push in the right direction.

Private Sub CommandButton1_Click()
If ActiveCell.Value = "DONE" Then
Rws = CStr(ActiveCell.Row)
Range("A:I").Cut
Sheets("LESS THAN $250M").Select
ActiveSheet.Range("B5").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("DONE").Select
Range("B").Select
Selection.Delete Shift:=xlUp
End If
End Sub