Macro to add new entry to growing list pls

**lister** · 03-23-2011, 11:42 AM

Hello

I want to add items to an existing list on a single worksheet. The item line(s) are from an external source which I put into a box at the top of the worksheet.

I record a keystroke macro, which works, but on next using the macro, it has "range-valued" the original last item, so instead of adding a new entry to the list, the macro replaces the last one entered via the macro.

Currently, I copy (Ctrl-C) the item "box", then:

Ctrl-end, Ctrl-left, down (arrow) - so to arrive on the last item, then down one line - then Ctrl-V the "box" into this "next empty cell".

Hey presto -except, as mentioned, the macro sets the "next empty cell" in concrete so just overwrites any previous entry arriving via the macro.

Help please?

PS I'm not up on Vis Basic. By "range valued", in DOS it'd be like using $ to fix the "next empty cell" which I don't want the macro to do, given it's supposed to be finding the latest entry, not the last one when I originally recorded the macro.

**Domski** · 03-23-2011, 11:51 AM

This will add the value from C1 to the end of a list in column A:

Please Login or Register  to view this content.

Dom

**lister** · 03-23-2011, 12:02 PM

Many thanks and more again for replying so quickly

I'm trying to figure out how to record that... Do I do a keystroke record, then step into to change it?

I can't find a tools/macro option that allows me to write one from scratch. Sorry if numpty.

Many thanks

**Domski** · 03-23-2011, 12:07 PM

It's not recorded code. The macro recorder would never come up with anything like that.

You would need to add a regular code module to your workbook's VBA project and then add the code to it.

Details can be found here: http://www.contextures.com/xlvba01.html

The full code would need to be more like this:

Please Login or Register  to view this content.

Dom

**lister** · 03-23-2011, 12:58 PM

Dom, I'll study the link and see what's what as it could be the answer to a few problems given it is irritating when keystroke macros don't perform as expected.

In my Lotus 123 days, their macros also had code that wasn't keystrokes and similarly one just had to learn it, so I feel a book purchase (or borrow from library) coming on also...

Thanks for the insight

**Rerock** · 04-22-2013, 11:27 AM

Originally Posted by Domski

It's not recorded code. The macro recorder would never come up with anything like that.

You would need to add a regular code module to your workbook's VBA project and then add the code to it.

Details can be found here: http://www.contextures.com/xlvba01.html

The full code would need to be more like this:

Please Login or Register  to view this content.

Dom

I ran a test, and this line of code works great! However, let's say that I wanted "Range("A" & Rows.Count).End(xlUp).Offset(1, 0)" to be located on a different workbook...

Open desired workbook, add Range("C1").Value to the desired workbook, save & close desired workbook.

Here is a sample of what I'm looking to do, but am not sure how to change it to work properly.

Can anyone help?

Please Login or Register  to view this content.

EDIT:
I figured out how to make it work, but I'm curious if/ how I could condense it down.

Please Login or Register  to view this content.

Macro to add new entry to growing list pls

LinkBack

Thread Tools

Rate This Thread

Display

Macro to add new entry to growing list pls

Re: Macro to add new entry to growing list pls

Re: Macro to add new entry to growing list pls

Re: Macro to add new entry to growing list pls

Re: Macro to add new entry to growing list pls

Re: Macro to add new entry to growing list pls

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions