MrExcel.com - Your One Stop for Excel Tips & Solutions

cut and paste macro - using active cell as start point...

Posted by Bill Robbins on December 10, 2001 12:18 PM

Can anyone tell me how to write a macro that starts in whatever cell is active, selects the next 49 cells below that in the column, and then copies and pastes that selection to the column to the right of the active cell?

I had most of it figured, but I couldn't figure out the code for "starting from the active cell"...

Regards,

Bill Robbins

Posted by Juan Pablo G. on December 10, 2001 12:53 PM

Try it out.

ActiveCell.Resize(49,1).Copy ActiveCell.Offset(,1)

Juan Pablo G.

Posted by Tom Urtis on December 10, 2001 12:54 PM

Hi Bill,

Use the ActiveCell object, for example in your case to copy the range into the next column:

Range(ActiveCell, ActiveCell.End(xlDown)).Copy
ActiveCell.Offset(0, 1).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False


Tom Urtis

Posted by Tom Urtis on December 10, 2001 12:56 PM

Neat suggestion Juan Pablo, definitely more code-efficient than mine!! (nt)

Posted by Juan Pablo G. on December 10, 2001 1:05 PM

Learned that trick from MrExcel himself (NT)

Posted by Tom Urtis on December 10, 2001 1:14 PM

One follow up please

Now c'mon, don't leave me hanging here!! How does the 49 become relevant, and is it a number code among other such number series for function/method activity?? Looks really efficient.

Posted by Bill Robbins on December 10, 2001 1:43 PM

Re: One follow up please

Thank you gentlemen.

An excellently efficient line of code, to be sure!

Regards,

Bill Robbins.

Posted by Juan Pablo G. on December 10, 2001 1:49 PM

Re: One follow up please

Tom, the resize event works exactly the same as selecting a range in a worksheet, if you take a look at the Name Box, you'll something like 5Rx3C, that's the same parameters that the Resize take, for example, Range("A1").Resize(5,3) It's really nice, and efficient, i agree.

Juan Pablo G. Now c'mon, don't leave me hanging here!! How does the 49 become relevant, and is it a number code among other such number series for function/method activity?? Looks really efficient.