Trying to use the command click button. Cell O2 has the invoice number 101,
O3 is 102, O4 is 103 etc. When I put an invoice number in cell E2 (101) and
the invoice name in F2, I would like to click on the button (Submit) and
advance the invoice name to P2. I know I can use the IF formula in Excel but
there is a lot more info that I would like to move with it and all at the
same time.
Thanks

The command button creates an event on the same worksheet code as the
button.
Firstly add a button using the ActiveX option (Excel 2007) or use the
control toolbox command bar, not the forms toolbar on the required
worksheet.
You can customize it by right clicking and selecting properties, change the
Caption to your text. 'Submit'
If you now double_click the button the code sheet for that worksheet will
open with the code as follows

Private Sub CommandButton1_Click()

End Sub

You can put your instructions into this sub routine. You might find it
useful to use the macro recorder whilst you do the transfer manually this
will create some code you can use as a template for for 'Submit program. One
thing you will find is that the the macro recorder will record the actual
row / column addresses of your copy process, you will need to change this so
it refers to the current row you are copying. Change this using the
ActiveCell.Row that way it will also copy the data from the row you have the
cursor in.

As an example for what you requested....(I presume when you say advance the
invoice name to P2 you mean copy it?)

Cells(Active.Row, "F").Copy Destination:= Cells(Active.Row,"P")

The above takes the value in column "F" for the row the cursor is in, and
copies it to column "P" in the same row.

HTH


--

Regards,
Nigel




"bgkgmg" wrote in message
...
Trying to use the command click button. Cell O2 has the invoice number
101,
O3 is 102, O4 is 103 etc. When I put an invoice number in cell E2 (101)
and
the invoice name in F2, I would like to click on the button (Submit) and
advance the invoice name to P2. I know I can use the IF formula in Excel
but
there is a lot more info that I would like to move with it and all at the
same time.
Thanks

This should work for you. You may have to customize this code to fit your
specification like Sheet names or ranges you wish to cut and paste. You will
have to insert a Command Button in a worksheet. The assign this macro to the
command button.

Sub MoveInvoice()

Const shOld As String = "Sheet1"
Const shNew As String = "Sheet1"
Dim LastRow As Long
Dim Invoices As Range
Dim cell As Range
Dim OldRange As Range
Dim NewRange As Range

'finds last used row in Col. E
LastRow = Sheets(shOld).Cells(Rows.Count, 5).End(xlUp).Row

'column E contains invoice numbers
Set Invoices = Sheets(shOld).Range("E1:E" & LastRow)

'cuts Invoice Name and pastes in Col. P
For Each cell In Invoices
'invoice name
Set OldRange = Sheets(shOld).Range(Cells(cell.Row, 6),
Cells(cell.Row, 6))
'new invoice name location
Set NewRange = Sheets(shNew).Range(Cells(cell.Row, 16),
Cells(cell.Row, 16))
'cut and paste
OldRange.Cut Destination:=NewRange
Next cell

End Sub

Hope this helps! If so, please click "Yes" below.
--
Cheers,
Ryan


"bgkgmg" wrote:

Trying to use the command click button. Cell O2 has the invoice number 101,
O3 is 102, O4 is 103 etc. When I put an invoice number in cell E2 (101) and
the invoice name in F2, I would like to click on the button (Submit) and
advance the invoice name to P2. I know I can use the IF formula in Excel but
there is a lot more info that I would like to move with it and all at the
same time.
Thanks