Command Button code

[lakle]

I have a 2 worksheets. One "Projects in Process"and the other "Project Completed". when the users fill in the information in cells A4-F4 (line 4 is the starting line and it could go down as far as line 23 or a23 - f23) In Projects in Process they press a "completed Project" command button and the data in a4-f4 copies over to worksheet "Projects completed". In the next available row (starting at row 4). Can somone help? I just don't understand how to do this. Please help me

[Simon Lloyd]

this can go in a standard module as i have done or put the body of it in a commandbutton click event:

Sub copy_data()
Dim Rng As Range, dRng As Range
Set Rng = Sheets("Sheet1").Range("A4:F" & Range("F" & Rows.Count).End(xlUp).Row)
Set dRng = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Rng.Copy Destination:=dRng
End Sub

change the sheets to suit!

[BradC]

Hi lakle.
Just to give me an idea of where you're coming from, how familiar are you with Excel Macro's? The task you're talking about will require three steps:

1. Writing VBA code to accomplish the task of moving the row of data
2. Creating a button in the first worksheet
3. Assigning a macro to the button

Is there one of these steps in particular you need help with, or all 3? Thanks

[lakle]

Hi Brad - 1 and 3 are where I need help. badly...

[Simon Lloyd]

Lakle if you have created the button then in design mode double click the button and paste the body of my code in but change the sheet names as required.

[BradC]

lakle,

Simon has done all the work for you. If you need help putting it together in your workbook, let us know. Here's the rundown. It sounds like you already have the button on your worksheet, but no code assigned to it.

1. Press Alt+F11 to enter design mode
2. Click Insert -> Module
3. Paste Simon's code into the new module (change "Sheet1" to the name of your Projects in Progress sheet, and "Sheet2" to the name of your completed projects sheet)
4. Close design mode
5. Right click the button on your worksheet, choose "assign macro", and assign the "data_copy" macro.

Also, you said you wanted to copy the data, but it sounds like you actually want to cut the data to the new sheet (so it's no longer in the first sheet after you move it). If this is the case, just change ".Copy" to ".Cut" in Simon's code.

[lakle]

I pasted the code and this is I got "expected end sub"

Private Sub CommandButton1_Click()
Sub copy_data()
Dim Rng As Range, dRng As Range
Set Rng = Sheets("Projects in Process").Range("A4:F" & Range("F" & Rows.Count).End(xlUp).Row)
Set dRng = Sheets("Project Completed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Rng.Copy Destination:=dRng
End Sub

[BradC]

Get rid of the first line, Private Sub blah blah blah...