Change link-source on file after copy the sheet into new a newworkbook

[johan]

Hello,

I like to have 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.

Why this action.... because my file has buttons on it. When I copy
the sheet with the buttons on it, it will get "links" to the original
file because of the VBA codes on it.

Possible ?? or another solution ?

greetings from John

 

[Dave Peterson]

I'm not sure what your buttons do, but you may want to replace the buttons from
the Forms toolbar with commandbuttons from the control toolbox toolbar.

You'll have to move the code (and modify it (probably)) to make it work. But
since the commandbutton's code is within the sheet module that owns the
commandbutton, it'll be copied when the sheet is copied.

 

[johan]

Hoi,

I'm using Excell 2003.
I'd made a worksheet with on it a couple of buttons created by the
menu-option view -> toolbars -> forms -> button and then linked to the
module which is written in the VBA screen of the sheet known as
"microsoft excel objects" -> Sheet1(template).

When I copy the sheet as written before (select sheettab -> left mouse
button -> create copy -> To new book) then the complete sheet
inclusive the buttons are copied to a new file. This works oke. In
this new file also the VBA code is copied with it. So this is also
oke.

But,... the buttons refers not to the VBA code in this new workbook,
but refers to the VBA code in the original file. Now you have a "link"
as you can see by menu-option Edit -> Links.

Now it is possible to solve the problem by hand:
or- 1) relink the buttons to the correct VBA code in this new file
(select the button -> left mousebutton -> assign macro -> select the
code within this copied workbook).
or- 2) save this new workbook, don't close it, and then go to menu-
option edit -> links -> change source and select this file.

My wish is to get a solution automatic. I wish to copy the worksheet
into a new workbook, save the file as mentioned before: "Template-date-
time.xls" (date and time as actual). and fulfill the menu-option edit
-> links -> change source and select this file.

hopely somebody can give me the solution.

regards,

 

[Dave Peterson]

I understood.

Did you reject the other suggestion?

 

[johan]

Dave,
I'm sorry. I didn't understood the first part of the description.
Now I'm understand it (use of embedded buttons) and....... it works
oke. No links anymore after copying the sheet into a new workbook.
This is really a simple solution.
THANKS.

regards,

 

[Dave Peterson]

There are benefits to each kind of button/commandbutton. And problems with
each, too.

But in your case, it just seemed to scream out for the commandbutton suggestion.

Glad it worked for you.