Data Validation
 

Hi,
is it possible to programme an option in a validation
list so that when the option, say "print", is selected
the last row of data entered on a worksheet is copied and
pasted into the next available row in another worksheet.
For example, when the "print" option is selected from the
drop down menu , all the data on cells A5:P5 are selected
(as this is the last info enterd on this worksheet) and
then paste it into cells A45:P45 in another worksheet as
this is the next available row !
Hope I have made myself clear and thanks in advance

I think I'd use a dedicated macro (maybe assigned to a button from the Forms
toolbar on a nice spot on that worksheet):

This assumes that the next open row can be determined by looking at column A.

Option Explicit
Sub testme()
Dim RngToCopy As Range
Dim DestCell As Range

Set RngToCopy = ActiveSheet.Range("a5:p5")

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

ActiveSheet.PrintOut preview:=True

RngToCopy.Copy _
Destination:=DestCell

'rngtocopy.clearcontents 'maybe????

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And remove the "Preview:=true" when you're done testing. (I didn't want to kill
too many trees while testing!)

Anthony wrote:

Hi,
is it possible to programme an option in a validation
list so that when the option, say "print", is selected
the last row of data entered on a worksheet is copied and
pasted into the next available row in another worksheet.
For example, when the "print" option is selected from the
drop down menu , all the data on cells A5:P5 are selected
(as this is the last info enterd on this worksheet) and
then paste it into cells A45:P45 in another worksheet as
this is the next available row !
Hope I have made myself clear and thanks in advance

--

Dave Peterson