Data Validation

[Anthony]

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 A55 are selected
(as this is the last info enterd on this worksheet) and
then paste it into cells A4545 in another worksheet as
this is the next available row !
Hope I have made myself clear and thanks in advance

 

[Dave Peterson]

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("a55")

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!)