Macro Question

Posted by Buck on February 02, 2002 12:23 AM

I've got a question about a small macro I've recorded.
I had it go to a named cell called "addcell" & then I
pressed my up arrow button 8 times so I could paste the
data, but in the macro, it wasn't recorded as going up
8 times, it just references the cell it ends up at. It
works fine the first time, but when I run it again, it
always goes up to A45, instead of going up just 8 rows.
What is the command to "Go Up 8" instead of the second
line in the following?:

Application.Goto Reference:="addcell"
Range("A45").Select
ActiveSheet.Paste

Thanks for your help!

-Buck-

Posted by StopsHere on February 02, 2002 12:55 AM

You can do it all with just one line of code :-
ActiveSheet.Paste Destination:=Range("addcell").Offset(-8, 0)

Or, even better, assuming the cell being copied is A1 :-
Range("A1").Copy Range("addcell").Offset(-8, 0)

Note that no selecting, activating, etc. is necessary.

Posted by Buck on February 02, 2002 3:28 PM

Thanks a bunch! One more question though... What would be
the line of code to add 8 rows - always before the
range "addcell"? TIA!

-Buck-

Posted by StopsHere on February 02, 2002 3:55 PM

Range("addcell").Resize(8, 1).EntireRow.Insert

Posted by Buck on February 03, 2002 1:16 AM

Re: Range("addcell").Resize(8, 1).EntireRow.Insert

Again, thanks for your help StopsHere... worked like a charm! I don't see this type of help in Excel - do you know of a good information source? Thanks again & have a good one! Later.....

-Buck- : Thanks a bunch! One more question though... What would be

Posted by StopsHere on February 03, 2002 6:24 AM

http://www.mrexcel.com/book.shtml

Posted by Buck on February 03, 2002 3:25 PM

Thanks!

Thanks again!