How to enable "Links" when copy a worksheet (with buttons on it) to anew book

[johan]

Hoi,


I'd created a simple worksheet in Excell with two buttons one it which
are related to a VBA code archived in the VBA screen of the worksheet.

I made a copy of my worksheet (select worksheet tab -> right mouse
button -> option Move/Copy -> Create copy to new workbook).

Now I see that my copied file has links (menubar -> Edit -> Links) to
my orinal file. This has to do with the buttons on it. When I delete
the buttons in the original file and then I made a copy, no links in
the copied file exist.

How can I make a copy of a file, with still the buttons on it, but
without a link to the original one.

Perhaps a new button with a VBA code that copies the file and links
directly the buttons to the new file name ??

or...... is there another solution to skip this problem ?

regards,
Johan

 

[Barb Reinhardt]

Try something like this

Sub CopyWorksheet()
Dim myWB As Excel.Workbook
Dim myShape As Excel.Shape
Dim i As Long

Dim myWS As Excel.Worksheet
Dim myNewWS As Excel.Worksheet

Set myWB = ThisWorkbook
Set myWS = myWB.Worksheets("Sheet1") '<~~~can change as needed

myWS.Copy

Set myNewWS = ActiveSheet

For i = myNewWS.Shapes.Count To 1 Step -1
Set myShape = myNewWS.Shapes(i)
myShape.Delete
Next i

End Sub

HTH,
Barb Reinhardt

 

[johan]

Thanks for the respons,

I'd tried the solution, but it copies the file without the buttons on
it and still with "links" (when looking by menuoption "edit -> links".

I want to try another solution as describe below. Can somebody please
help me out.

I need a macro that made a copy of the actual sheet into a new
workbook (create a copy -> new book).
Then the macro has to save directly the file into the directory C:/
Apps with the name "Template-date-time.xls" (date and time as actual).
After that the action should be taken: menuaction "Edit -> Links ->
Change Source" and then change the source to the before saved
filename.

Then the file has, after copy the sheet with the buttons no "links"
anymore to the original file.

Possible ?? or another solution ?

regards,
Johan