Use Macro to add data to end of a list

[graham1607]

Hi Guys

I'm afraid I'm a complete macro beginner, and would be very grateful for your help with this simple problem.

I have a very long list of data in several columns. I want to be able to enter new entries in separate area of the sheet with the same layout (like a standard entry form), then press a macro button to copy the data to the end of the list and clear the form (i.e. the macro needs to be able to know the length of the list with something similar to a COUNT function (I can stick this in a cell if macros can use indirect references...?)).


***
e.g. if my long list is:

Joe Bloggs / 18 / Manager / Male
...
May Yates / 36 / CEO / Female

I'd like to be able to add in my little form:

Colin Jackson / 27 / Cleaner / Male

and press a button to stick Mr. Jackson at the bottom of the list.

***

Can anyone kindly help?

Many thanks

Graham

[VBA Noob]

Before going down the macro root have you looked at Forms.

Data > Forms.

VBA Noob

[graham1607]

Thanks - the list is actually a lot more complicated than my example, and I have to select from lists for some of the cells. That's why it's better if possible to use a macro. But if you can think of an alternative, I'd be very grateful

Best

Graham

[VBA Noob]

Take a look at the 2 x parts examples here and see if this is the sort of thing your after

http://www.contextures.com/excelfiles.html#UserForm

VBA Noob

[Norie]

Graham

Why don't you actually use a form? ie a userform

By the way there are various methods to get the next/last row of data.

Here's one.

LastRow = Range("A" & Rows.Count).End(xlUp).Row

LastRow = Range("A" & Rows.Count).End(xlUp).Row+1

[graham1607]

It looks like it might, if one list could be conditional on another (that's how I've got two cells set up at the moment). Also, because I'm not au fait with forms, I don't know how to find the form the button is pointing to, and work out how it is set up. Maybe I need to be pointed to a simple form tutorial? But if the macro option is easy (i.e., if I can just do a simple copy, with the row number to copy to dictated by a COUNT function) that would be good enough and quick to boot. Is this a lot more difficult than it sounds?

Thanks

Graham

[Norie]

Graham

Why do you need COUNT?

Did you not see what I posted?

PS Doesn't the link VBA noob posted have appropriate examples?

It's kind of the standard link people point towards regarding userforms.