Macro that kills a macro when a new workbook is opened. [Solved/Closed]

JStill - Jul 31, 2015 at 03:04 AM - Latest reply: ac3mark 10542 Posts Monday June 3, 2013Registration dateModeratorStatus September 24, 2018 Last seen
- Aug 17, 2015 at 04:48 PM
Hi All,

I am still new at excel macros. I am using a command button to copy all work sheets in a workbook then pasting just the values into a new workbook, and then deleting the command button. The problem I'm having is that the macro command button is being deleted in the new workbook, but I am receiving a Run-Time Error '-2147024809 (80070057)': The item with the specified name wasn't found . When i Debug the error, the ActiveSheet.Shapes ("CommandButton1").Delete becomes highlighted. How can I fix this issue? Here is my code.


Private Sub CommandButton1_Click()
Dim websheet As Worksheet

ActiveWorkbook.Sheets.Copy

For Each websheet In ActiveWorkbook.Worksheets
websheet.UsedRange.Copy
websheet.UsedRange.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

ActiveSheet.Shapes("CommandButton1").Delete

Next

End Sub



Thanks,
JS
See more 

5 replies

ac3mark 10542 Posts Monday June 3, 2013Registration dateModeratorStatus September 24, 2018 Last seen - Jul 31, 2015 at 08:50 AM
0
Thank you
It is telling you that it has accessed a sheet that does not contain the button. Perhaps you can check first to see if the button exists, and if it does, then destroy it!


(Change for your language, but theory still same!):


if "CommandButton1" != NULL 
"CommandButton1".Delete
end if


I have said it once, I will say it again. IT!
JStill 2 Posts Friday July 31, 2015Registration date August 14, 2015 Last seen - Jul 31, 2015 at 11:12 PM
Thanks for the reply! Unfortunately this did not fix the issue. When using the code it returns a syntax error then highlights the if statement. Is there a way to select the sheet in the new workbook that is created from the macro that contains the commandbutton THEN delete it?
ac3mark 10542 Posts Monday June 3, 2013Registration dateModeratorStatus September 24, 2018 Last seen - Aug 14, 2015 at 08:17 PM
PLease forgive me for the delays in replies. PLease understand that it was more of an example of logic and how to think of the solution, than and actual TURNKEY solution to cut and paste!
JStill 2 Posts Friday July 31, 2015Registration date August 14, 2015 Last seen - Aug 14, 2015 at 08:41 PM
Hello, I understand ya, and all input is good for someone like me who is new to this, and actually, it helped out and this issue is now resolved. Thanks for the help!
ac3mark 10542 Posts Monday June 3, 2013Registration dateModeratorStatus September 24, 2018 Last seen - Aug 17, 2015 at 04:48 PM
Great Job!