MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro copying Buttons


Posted by RoB on August 14, 2001 8:08 PM

I have a macro that copies data from one sheet to another. It works good, but it also copies the buttons from the first sheet, which I dont want it to do. Is there a way to prevent it from copying buttons?
Thanks


Posted by neotech on August 14, 2001 8:40 PM

rob,

if you recorded the macro then likely there's a line of code like 'cells.select' that refers to the range of cells you're copying to the next page. using that line will copy everything including the controls. then if you right-clicked the access the copy option, there'll be line like 'range("A1").select'. if this is the case, delete these two lines (otherwise just the first one) and replace them with 'range("a1:z100").select', where ("a1:z100") is the name of the range containing the data you want to copy. doing it this way won't copy the controls with it, even if they;re within the range you've specified...

neo

Posted by RoB on August 14, 2001 9:01 PM

there are my macro copy instructions...how do i fix them to not copy buttons?

Here is part of the macro I'm using. How would I modify this to prevent it form copying buttons?:

Dim RangeSelect As String

RangeSelect = "A1:" & Range("A1").End(xlToRight).End(xlDown).Address

Sheets(CurrentSheetName).Range(RangeSelect).Copy _
Destination:=Sheets("Prelim Sort").Range("A1")


Thanks

Posted by Robb on August 14, 2001 9:07 PM

Re: there are my macro copy instructions...how do i fix them to not copy buttons?

Rob

Another way, unless you have any reason not to, is to set the button property to "don't move or size with cells" should also stop them copying.

Regards

Posted by RoB on August 14, 2001 9:13 PM

well...do i feel dumb now...thanks a lot! again :)

Another way, unless you have any reason not to, is to set the button property to "don't move or size with cells" should also stop them copying. Regards

: Here is part of the macro I'm using. How would I modify this to prevent it form copying buttons?