Macro Questions:

[Captain Ordinary]

Hi there

Hopefully these questions wont sound too primitive:

a) I've recorded a very simple macro that copies a row of date (C10 to O10)
and pastes (paste special - values only) into a master work book.
The entry point in the master work book is the current active cell; each new
row of data goes into a new row (B4, B5, B6 etc...) slowly building a table.
My question is how do I make the macro end with the active cell in the next
row in B column - so I can run the same macro again on the next spreadsheet?

b) How wise is it to use the 'save' command in a macro?

c) How do I keep the mini macro 'toolbar' always-on-top?
For some unexplained reason mine's gone, and to stop recording I need to
Tools - Macro - Stop recording?

Cheers
CO

 

[Mallycat]

Assuming it is just the next row, then add this code to the bottom of
you macro.

activecell.offset(1,0).select


b) How wise is it to use the 'save' command in a macro?
No problems, but you need to make sure you have tested the macro before
you do this (don't want to save garbage over good info).

c) How do I keep the mini macro 'toolbar' always-on-top?
For some unexplained reason mine's gone, and to stop recording I need
to
Tools - Macro - Stop recording?

Right click on one of the other toolbars while recording a macro, and
you should see the tool bar in the list "Stop Recording"

Matt

Cheers
CO

 

[Maistrye]

Hi there

Hopefully these questions wont sound too primitive:

a) I've recorded a very simple macro that copies a row of date (C10 to
O10)
and pastes (paste special - values only) into a master work book.
The entry point in the master work book is the current active cell;
each new
row of data goes into a new row (B4, B5, B6 etc...) slowly building a
table.
My question is how do I make the macro end with the active cell in the
next
row in B column - so I can run the same macro again on the next
spreadsheet?

b) How wise is it to use the 'save' command in a macro?

c) How do I keep the mini macro 'toolbar' always-on-top?
For some unexplained reason mine's gone, and to stop recording I need
to
Tools - Macro - Stop recording?

Cheers
CO

a) I'm assuming your worksheet is referenced by a variable, say
aWorksheet.

Do something like:
aWorksheet.Cells(row,column).Activate
or
aWorksheet.Cells(row,column).Select

(I can't recall the technical difference between these two... both
should work for your purposes though.)

b) I've been taught to save often, so I'm probably the wrong person to
ask. Once you're sure the macro does what you want it to do, save
away. It's wise to make sure you have a backup copy of any raw data
you may be using just in case something unforeseen happens while you're
developing your macro.

c) Right click on the toolbar at the top, you should get a list of the
available tool bars. Select Visual Basic. It should appear somewhere
in the middle of your screen. At this point, just drag it and place it
on your toolbar (for example, beside or under or above the other tool
bars that are normally near the top of the screen). (Hope this is
clear... sometimes my terminology is lacking)

Scott

 

[Captain Ordinary]

Thank you very much for your advice.

Good health to you both

Cheers
CO