Macro that kills a macro when a new workbook is opened.

JStill - Jul 31, 2015 at 03:04 AM
 Blocked Profile - 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


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



End Sub


1 reply

Blocked Profile
Jul 31, 2015 at 08:50 AM
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 
end if

I have said it once, I will say it again. IT!
JStill Posts 2 Registration date Friday July 31, 2015 Status Member Last seen August 14, 2015
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?
Blocked Profile
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 Posts 2 Registration date Friday July 31, 2015 Status Member Last seen August 14, 2015
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!
Blocked Profile
Aug 17, 2015 at 04:48 PM
Great Job!