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

- - Latest reply: ac3mark
Posts
10921
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 10, 2018
- 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 

1 reply

Posts
10921
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 10, 2018
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
Posts
2
Registration date
Friday July 31, 2015
Last seen
August 14, 2015
-
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
Posts
10921
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 10, 2018
-
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
Posts
2
Registration date
Friday July 31, 2015
Last seen
August 14, 2015
-
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
Posts
10921
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 10, 2018
-
Great Job!