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

Report
-
 Blocked Profile -
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

1 reply


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!
Posts
2
Registration date
Friday July 31, 2015
Status
Member
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?
Blocked Profile
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!
Posts
2
Registration date
Friday July 31, 2015
Status
Member
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!
Blocked Profile
Great Job!