Automating an existing macro

**BigMcBen** · 12-02-2011, 05:31 AM

Hi All. I have a situation where every night, 20-30 lines of info is sent to me. I have to take each line and copy to another sheet in the same workbook and then manipulate that info within this sheet. I have created a macro to do most of this but there are two things I'd like to do. Instead of Rows("1:1").Select, I'd like it to be whatever cell is selected in the sheet when the macro is run. Secondly, at the end when it selects the next cell down, if there's data in the cell then repeat the macro, if the cell is empty then stop. If sure that both of these requests will be possible, just need someone with a bit more knowledge than I've got. Thanks in advance.

Please Login or Register  to view this content.

**Marcol** · 12-02-2011, 08:16 AM

I'm not to sure what you are really trying to do, so this is only a guess.

See if this does what you need

Please Login or Register  to view this content.

Can you post a sample of your data as it is recieved, along with another sheet showing the result you need?

Does the incoming data overwrite any older data in the source sheet?
Does the new data get appended to the destination sheet?

P.S.
You're not a Tube, just from Edinburgh! ...

**BigMcBen** · 12-02-2011, 11:54 AM

Marcol

What happens every night is head office send us collections for the next day. These have be transfered individually to the collection sheet and then printed off. My macro takes the selected row in sheet 2, copies and pastes it into Sheet 1, maniplulates it and then prints off 2 copies before switching back to sheet 2. Then you manually have to press down arrow, shift+space then ctrl+j to start the macro again for the next line and so on. What I'm looking for is the code so that you hightlight the first cell of the new list of collections you just start the macro and it when it switches back to sheet 2, moves down to the next line and carries on until it finds a empty cell and stops. Is this possible?

**Leamc** · 12-02-2011, 12:27 PM

BigMcBen, I am sure that the guru's on this forum would be able to help with your VBA code.

An alternative you could think of is using the mail merge function in Word. The basic function is that you create your form that needs to print. You link the colums in the excel to the field in your form. When you are happy with the form you create 30 letters at once and print.

This template could be used over and over again, just refresh the link to excel.

Something to think about.