VBA to delete specific objects in worksheet

[traub86]

I am having trouble creating VBA to delete specific objects in a worksheet before I copy that worksheet to a new workbook. I have several macro buttons and signature blocks in worksheet1 and when I click my export button I need the macro buttons to go away but the signature blocks to stay there. I tried going into Advanced settings and unchecking "Cut, Copy, and sort inserted objects with their parent cells" but the signature blocks go away as well. Any help would be greatly appreciated.

 

[steve the fish]

Like this?

Sheets("Sheet1").Copy
ActiveSheet.Buttons.Delete

 

[traub86]

That didn't work. I played around with it for a bit to see if I could uncheck the "Cut, Copy, and sort inserted objects with their parent cells" in advanced options but it doesn't bring the signature blocks over. Do you think I could forego the signature blocks in the initial sheet and then add them in the destination sheet within the VBA code?

 

[traub86]

Here's what I have and am trying to insert 8 signature lines in 2 rows across 4 columns over range A42:AB55:

Sub ExportToMasterFileChalk5()
    
    Workbooks("JMPI MANIFEST.xlsm").Sheets("Chalk 5").Copy before:=Workbooks("DO NOT DELETE_MASTER JMPI MANIFEST.xlsm").Sheets(1)
    Workbooks("DO NOT DELETE_MASTER JMPI MANIFEST.xlsm").Sheets(1).Name = Format(Date, "DDMMMYYYY") + " Chalk 5"
End Sub

 

[NdNoviceHlp]

My understanding is that you are trying to remove command button(s). Is it an activeX or form control? Doesn't really matter. Use the following code to loop through and identify the buttons and type of buttons that you want to remove. Then remove (comment out) the message box and use the code to delete the button(s) adjusting the type as necessary. Just remove the 1st character from each line of the code that deletes the button (ie. that part of the code has been commented out). HTH. Dave

Sub RemoveButton()
Dim Sh As Shape
With Sheets("Sheet1") 'adjust to suit
For Each Sh In .Shapes
'type 12 activex command button
'type 8 form command button
MsgBox Sh.Name & " Type# " & Sh.Type
'If Sh.Type = 8 Then 'remove form button
'Sh.Delete
'End If
Next Sh
End With
End Sub