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            
                

    

        



            

                
                
            

            

    

    
            

            

    

    

        
        

                
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!
                
                

    


            
    

        
        
    


        

        


    




        

                    

                    


            
    
    

        


    

                    

    
        
    
    

                
            

    


    
    

                

        
                    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!
    




    

            
    

        
        
    



    

    
            

        

        

    

        

            
        

    

    

    

        

    

    
    





    
Similar discussions

                                                    

                
                    How to modify the macro code for new structure of table
                    

                        
    
    

            

    


                        

                                                                                    jpppol -  

                            
                                                                                    TrowaD - 
                        

                    

                    

                    

                        9 responses
                    

                
            

                                                        

                
                    How to create a macro to copy and paste
                    

                        
    
    

            

    


                        

                                                                                    johncarr1973 -  

                            
                                                                                    ac3mark - 
                        

                    

                    

                    

                        3 responses
                    

                
            

                                                            

                    

    
    

        
        
    

                

                        

                
                    Create Multiple Workbooks With Template At Once
                    

                        
    
    

            

    


                        

                                                                                    Dak903 -  

                            
                                                                                    Dak903 - 
                        

                    

                    

                    

                        4 responses
                    

                
            

                                                        

                
                    Convert data with rows into columns
                    

                        
    
    

            

    


                        

                                                                                    pooja.bs -  

                            
                                                                                    TrowaD - 
                        

                    

                    

                    

                        2 responses
                    

                
            

                                                        

                
                    Create a macro to open workbooks
                    

                        
    
    

            

    


                        

                                                                                    Fazil27 -  

                            
                                                                                    Fazil27 - 
                        

                    

                    

                    

                        2 responses