Move shape(button 1) from active sheet to next sheet

[tubrak]

Hi experts

I have code create sheets . what I want procedure to put in the end of the code to move shape(button 1) from active sheet to next sheet after the code create new sheet added by code . I don't want copying button 1 , just move from active sheet to next sheet .

example: active sheet is STOCK and move the button 1 to the new added sheet will be STOCK_JAN and when STOCK_JAN is active and move the button 1 to the new added sheet will be STOCK_FEB and so on .

thanks

 

[John_w]

See if you can incorporate this code into your code. It moves the "Button 1" shape from the active sheet to a newly added sheet (newSheet) in the same position.

Public Sub Move_Button()
    
    Dim newSheet As Worksheet
    Dim buttonLeft As Single, buttonTop As Single
    
    With ThisWorkbook
        With .ActiveSheet.Shapes("Button 1")
            buttonLeft = .Left
            buttonTop = .Top
            .Cut
        End With
        Set newSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
    End With
    
    With newSheet
        .Activate
        .Paste
        With .Shapes("Button 1")
            .Left = buttonLeft
            .Top = buttonTop
        End With
        .Range("A1").Select
    End With
    
End Sub

 

[tubrak]

thanks, but I don't want adding new sheet because I have macro deos it as I said , just move the button from active sheet to next sheet(is already existed by my code) .

 

[John_w]

I know you don't need the code to add a new sheet (1 line), but I had to reference the new sheet object (newSheet) to show how the button shape is pasted and positioned on it. Hopefully there's enough code for you to incorporate into your own code.

 

[tubrak]

but I had to reference the new sheet object (newSheet) to show how the button shape is pasted and positioned on it.

it means there is no another way to do that without add new sheet, right?

Hopefully there's enough code for you to incorporate into your own code

unfortunately this doesn't help me with my project !
because of my code adds new sheet contains formatting and borders and data depends on active sheet .so your code create empty sheet and instead of transfer the button to my added sheet by my code will move to new sheet by your code and I will not benefit from it in any way .
any way thank you for your trying to help me .

 

[John_w]

it means there is no another way to do that without add new sheet, right?

No. Adding a new sheet was just for the purposes of my demonstration code.

Use a Worksheet object to reference your newly added sheet - newSheet in my example. Cut the button shape from the active sheet (you might have to activate it first), activate the new sheet and Paste and position the button on the new sheet - as shown by my code. Cutting and Pasting has the advantage of automatically carrying over all the properties (Name, Caption, OnAction, etc.) of the old button to the new button.

Another way is to Add the new button and Delete the old button, but you have to set the properties of the new button explicitly. This method is shown in this macro:

Public Sub Add_Sheet_Move_Button()

    Dim oldButton As Button, newButton As Button
    Dim newSheet As Worksheet
    
    With ThisWorkbook
        Set oldButton = .ActiveSheet.Buttons("Button 1")
        Set newSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
    End With
        
    With oldButton
        Set newButton = newSheet.Buttons.Add(.Left, .Top, .Width, .Height)
        newButton.Name = .Name
        newButton.Caption = .Caption
        newButton.OnAction = .OnAction
        'Set other button properties as required
        .Delete
    End With
       
End Sub

 

[tubrak]

it gives Runtime error 1004 Unable to set the name property of the button class

newButton.Name = .Name

 

[John_w]

Please post your whole code which causes that error.

 

[tubrak]

I just use your code in post #6 !

 

[John_w]

The code works perfectly for me and Googling that error the only cause is attempting to set the Name property longer than 32 characters.