Hi
I'm sorry in advance - I'm a novice and I am stuck with re-writing the
following code without using a hard code worksheet name, or "current
worksheet" or "active worksheet" or "selection"... (I must avoid using these
words as they are apparently causing problems macros assigned to buttons)

The code needs to:
- make a copy of the current worksheet - whose name appears in cell BP8
- put the copy into a separate workbook
- select the named range "PlanCPTrange" on the copied worksheet (only) and
paste special as values
- select all buttons on the copied worksheet (only) and delete them
- select cell A1 (without using the word "select"
I tried to do this myself, but failed:

Sub CopyPlan()
Dim mySheet As Worksheet (?????)
Dim DestSheet As Worksheet (????)

Set mySheet = Range("BP8").Value 'mySheet worksheet name is quoted in BP8
on the current worksheet
Set DestSheet = ???? 'the name of the copied mySheet i.e. if BP8 = "Test"
then it will be "Test (2)"

With mySheet
Copy After:=Workbooks(????).Sheets(1) 'how to copy mySheet worksheet
into a new workbook
End With

With DestSheet.Range("PlanCPTrange")
Cells.Copy
Cells.PasteSpecial Paste:=xlValues
End With

With Destsheet.Buttons
.Delete
End With

End Sub

--
Thank for your help
BeSmart