How do I create a picklist macro?

[Guest]

I want to choose a row of data, paste it into a different worksheet, and have
the original worksheet update that a change has been made. It would
essentially act like a picklist. I pick the data from a particular row, it
automatically copies and pastes some of the fields to a new worksheet
underneath the last row of data copied and pasted. I try to just link
worksheets but this won't work because it creates 0's all over the linked
sheet. Thank you so much for your help!

 

[Gord Dibben]

Sub findbottom_paste22()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Application.InputBox(prompt:= _
"Select a range", Type:=8)
Set rng2 = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
..Offset(1, 0)
rng1.Copy Destination:=rng2
End Sub

BTW........you can create links without the zeros.

=IF(Sheet1!A1="","",Sheet1!A1)

I'm unsure what "have the original worksheet update that a change has been made"
means.


Gord Dibben MS Excel MVP

 

[Bill Renaud]

For this application/macro, I would first select a cell on the desired row,
then click a button on a commandbar somewhere to call the macro and carry
out your actions.

So your pick list is on the worksheet that the data was copied to? You
would have to update the range of used data after you are finished copying
the data.

 

[Guest]

Thank you both for your help. I really appreciate it. I'm new to VBA
programming but I need to learn quick! Do you suggest any user friendly
books?
Regards,
-Bobby

 

[Bill Renaud]

<<Do you suggest any user friendly books?>>

I would definitely start with any version of "Excel XXXX Power Programming
with VBA" by John Walkenbach, unless you are using Excel 2007. From what I
hear, the ribbon bar in Excel 2007 is totally different than all previous
versions, so you probably need a book on Excel 2007 if you are using that
version. Otherwise any version of book will do for general VBA programming,
so you could maybe find one at a used book store. Hopefully, a used copy
would still have the CD-ROM in the back!

You might also consider "VBA Developer's Handbook", by Ken Getz and Mike
Gilbert. I have the 1997 edition, and the Introduction contains over 20
pages of an interview the authors did with the Program Manager for the
Visual Basic group at Microsoft. It contains a lot of insights into how VBA
works and is helpful in understanding the history, future and present
details of VBA. It also contains the "Reddick VBA Naming Conventions" in
one of the Appendixes, which is a great way to start using consistent names
for all of your variables. (Makes reading newsgroup posts easier!)

If you do (or plan to do) any significant work with Forms, then a book on
Access, such as "Access 97 Developer's Handbook" by Paul Litwin, Ken Getz,
and Mike Gilbert is also quite handy as a reference.