Problems with automatic "Advice notes".

[Steve Shakeshaft]

I'm a new user to XL2000, but I have managed to get an automated advice note system to nearly work! I input details of the goods to be shipped into an input zone, press "ctrlW" and the macro- Writes the information into the Advice note, pastes a copy to a different worksheet as a "record", prints the advice note out and saves the file. I need to do the following changes but do not know how.

1 Before it saves I need to update a cell by incrementing a "counter" by 1 to prepare for the next advice note.

2 When it pastes the "record" copy it always overwrites the prviously pasted copy.

3 I have a worksheet with about 100 addresses, the "consignees". I can't find a way to "select a consignee" and paste the result into the advice note.

Thanks for your help, I hope these are not too many questions for my first post.

[Mark O'Brien]

On 2002-05-01 12:40, Steve Shakeshaft wrote:
I'm a new user to XL2000, but I have managed to get an automated advice note system to nearly work! I input details of the goods to be shipped into an input zone, press "ctrlW" and the macro- Writes the information into the Advice note, pastes a copy to a different worksheet as a "record", prints the advice note out and saves the file. I need to do the following changes but do not know how.

1 Before it saves I need to update a cell by incrementing a "counter" by 1 to prepare for the next advice note.

2 When it pastes the "record" copy it always overwrites the prviously pasted copy.

3 I have a worksheet with about 100 addresses, the "consignees". I can't find a way to "select a consignee" and paste the result into the advice note.

Thanks for your help, I hope these are not too many questions for my first post.

1. Let's assume that the counter is on Sheet1 cell A1, you would put this before your save command:

Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1

2. Yup. This doesn't sound like this is what you want it to do. So what do you want it to do?

3. What ways have you tried and what kind of solution are you looking for?

Basically, we need a bit more detail on 2 and 3. Both sound very doable. Not a bad attempt at a first post.

[Steve Shakeshaft]

On 2002-05-01 13:35, Mark O'Brien wrote:

1. Let's assume that the counter is on Sheet1 cell A1, you would put this before your save command:

Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1

2. Yup. This doesn't sound like this is what you want it to do. So what do you want it to do?

3. What ways have you tried and what kind of solution are you looking for?

Basically, we need a bit more detail on 2 and 3. Both sound very doable. Not a bad attempt at a first post.

Mark, Well, I find your last comment most flattering! Anyhow, yes Item #1 now sorted thanks.

Now item #2- More specifically, I need each run of the macro to save the advice note alongside/below the previous one. So that I have a ready back up or reference for each advice note.

item #3- I've tried various themes around a macro to "Select the row containing the consignee details (mainly address), copy it, change to the advice note worksheet, paste the consignee details." Trouble is then that the macro selects the same details each time and pastes the entire copied row across the worksheet. So I need to "select the (1 out of 100+ rows) row, copy the details, paste and format (somehow) to fit the advice note properly."

[Mark O'Brien]

What's an advice note? IS it just text in a cell? Is it a comment? Is it a textbox? OR something more sinister?

[Steve Shakeshaft]

On 2002-05-01 20:45, Mark O'Brien wrote:
What's an advice note? IS it just text in a cell? Is it a comment? Is it a textbox? OR something more sinister?

Sorry about that, when I ship out a refurbished pump or valves or something, it is the piece of paper that advises the shipper where to take it and the consignee what's inside the box. So, more related to XL, it is a range of cells formatted to look like a form when printed out.

_________________
Thanks and regards

Steve
steve@ukbeg.com

[ This Message was edited by: Steve Shakeshaft on 2002-05-02 01:00 ]

[Tom Schreiner]

Steve, email the sheet if you'd like.
TsTom@HotMail.Com
Tom

[Steve Shakeshaft]

On 2002-05-02 01:04, TsTom wrote:
Steve, email the sheet if you'd like.
TsTom@HotMail.Com
Tom

OK, I'll do that. I have managed to fix problem #3. Now I am real stuck with problem #2.


_________________
Thanks and regards

Steve
steve@ukbeg.com

[ This Message was edited by: steve shakeshaft on 2002-05-02 03:41 ]