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

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 wrote:
>
> 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

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,

I understood.

Did you reject the other suggestion?

johan wrote:
>
> 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

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,

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.

johan wrote:
>
> 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