Previous Topic Next Topic

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

Solved/Closed
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



   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            
                

    
    

    
            

            

    

    
    

        Related:         
    




    

        
        

                
1 response

        

        
    


            

        
        
        

                        Answer 1 / 1
        


        
                    

                

        

                    

        
    
    

            

    

        

            

    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 
"CommandButton1".Delete
end if




I have said it once, I will say it again. IT!
                
                

    
        

    
            
        0
        
            
                
    

            



    

        
        
    


        

        


    




        

                    

                    


            
    
    

        



    

                    

    
        
    
    

                
            

    


    
    

                

        


     
             

                    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?
    




    
 
    
        
    0

                    
        
    
    

            



    

        
        
    



    

    
            

        

                    


            
    
    

        



    

                    

    
        
    
    

            

    


    
    

            

    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!
    




    
 
    
        
    0

                    
        
    
    

            



    

        
        
    



    

    
            

        

                    


            
    
    

        



    

                    

    
        
    
    

                
            

    


    
    

                

        


     
             

                    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!
    




    
 
    
        
    0

                    
        
    
    

            



    

        
        
    



    

    
            

        

                    


                
        
    
    

        



    

                    

    
        
    
    

            

    


    
    

            

    Blocked Profile



    

    Aug 17, 2015 at 04:48 PM

    






    Great Job!
    




    
 
    
        
    0

                    
        
    
    

            



    

        
        
    



    

    
            

        

            

        

    



    
Similar discussions

                                                    

                
                    Automatically Run Macro from a calculated cell value change
                    

                        
    
    

            

    


                        

                                                                                    smiller -  

                            
                                                                                    TrowaD - 
                        

                    

                    

                    

                        1 response
                    

                
            

                                                        

                
                    Execute Macro On Calculated Cell change
                    

                        
    
    

            

    


                        

                                                                                    Notar -  

                            
                                                                                    rizvisa1 - 
                        

                    

                    

                    

                        5 responses
                    

                
            

                                                            

                    

    
    

        
        
    

                

                        

                
                    Code for macro that conditionally inserts rows
                    

                        
    
    

            

    


                        

                                                                                    bfleming -  

                            
                                                                                    bfleming - 
                        

                    

                    

                    

                        4 responses
                    

                
            

                                                        

                
                    How to convert number into text in Excel
                    

                        
    
    

            

    


                        

                                                                                    boratechandu -  

                            
                                                                                    Raj - 
                        

                    

                    

                    

                        9 responses