Is there a command to update data from one sheet to another when both sheets
are located in two different places? I have a workbook that needs to be
updated every three hours; the data is located in a work book in another
drive. I was wondering if is even possible to create a button that when
pressed updates the data automatically.
--
Regards
YM

On Jun 20, 8:59 pm, Yitzhack
wrote:
Is there a command to update data from one sheet to another when both sheets
are located in two different places? I have a workbook that needs to be
updated every three hours; the data is located in a work book in another
drive. I was wondering if is even possible to create a button that when
pressed updates the data automatically.
--
Regards
YM

Record a macro that opens the source file, then opens the dependent
file, calculates (if auto-calc is not on), saves the dependent file,
and closes both. Assign it to a button.

Or, you could put the button in the dependent file and just have it
open the source file, calculate if necessary, then close the source.

There is more complicated code that could be done as well. Beyond my
ability.

Spiky -

Thanks for the response, but is here is the part where i need the help. How
do i do that? (recording the macros and the button?).

Thanks for the help.
--
Regards
YM



"Spiky" wrote:

On Jun 20, 8:59 pm, Yitzhack
wrote:
Is there a command to update data from one sheet to another when both sheets
are located in two different places? I have a workbook that needs to be
updated every three hours; the data is located in a work book in another
drive. I was wondering if is even possible to create a button that when
pressed updates the data automatically.
--
Regards
YM

Record a macro that opens the source file, then opens the dependent
file, calculates (if auto-calc is not on), saves the dependent file,
and closes both. Assign it to a button.

Or, you could put the button in the dependent file and just have it
open the source file, calculate if necessary, then close the source.

There is more complicated code that could be done as well. Beyond my
ability.

ToolsMacroRecord new macro.

ViewToolbarsForms.

Click on the button icon, draw a button and assign the macro to that button.


Gord Dibben MS Excel MVP

On Mon, 23 Jun 2008 21:29:01 -0700, Yitzhack
wrote:

Spiky -

Thanks for the response, but is here is the part where i need the help. How
do i do that? (recording the macros and the button?).

Thanks for the help.

Spiky -

Thanks for the response, but is here is the part where i need the help. How
do i do that? (recording the macros and the button?).

Thanks for the help.
--
Regards
YM


Oh, sure. I like to use the Visual Basic toolbar when recording for
ease of use. So open that up by right-clicking on a toolbar and
selecting it from the menu. I have Excel 2003, so all this applies to
that version and most earlier versions.

First, plan it out. Make sure you know each step you want so you can
do them easily. Don't do anything extra, or it will record that, too.
The one we've discussed is very short, so this shouldn't be a big
deal. If you want it to open both files for you, make sure they are
both closed before you start recording. If you want to have the one
file open and run the macro from there (I mean in the future), then
open that file and be looking at it before starting.

Second, you may need to adjust your Macro Security if you have never
used any before. I think that is a button on the toolbar, or it is in
the menu: Tools|Macro|Security. Choose medium security, probably. If
you have XL2007, there is a completely different way to do this.

Record:
1) Click the Record button on the toolbar. (red dot)
2) Give the macro a name and place to save. I don't think there can be
spaces in the name, so use underscore if you want a space. If you want
it just for your computer, choose Store in Personal Macro Workbook. If
you want it attached to the file so it can be used elsewhere, choose
This Workbook.
3) Do everything you need to do. Open file, hit F9 (calculate), save,
etc. Speed doesn't matter, but do it in the right order and don't miss
anything or add anything you don't want.
4) Click the Stop button. (same button, now a square)
5) If you store it in Personal Macro Workbook, Excel may ask to save
that when you quit Excel next. Be sure to say Yes.

Make a toolbar button (if you store in Personal Macro Workbook):
1) Right-click a toolbar and select Customize from the bottom of the
menu. Go to the Commands tab. In the first pane, select Macros. In the
second pane, drag the Custom Button to wherever you would like it on a
toolbar. Don't close the Customize window, leave it open for now.
2) Right-click on your new button and select Assign Macro. Choose your
new macro from the list.
3) You can also right-click and change the name of the button (you'll
see the name in tooltips in the future), change the picture on the
button, etc. if you like.
4) Now close Customize window.

Make a button in a file (if you store the macro in one of the files):
1) Open the Drawing toolbar by right-clicking on any toolbar.
2) Click on Text Box toolbar button. Draw a text box where you want
your macro button to be.
3) Type in whatever text you want.
4) Right-click on the text box. You may have to point at the very edge
of it to right-click. Select Assign Macro from the menu and choose
your macro.
5) Format the box however you want. Background color is very helpful,
Center-orienting the text makes sense, adjust the size, I like shadows
or 3D effect to make it actually look like a button, etc. (right-click
and select Format)

Spicky -

Thank you for taking the time, i really apreciated. BTY, it works, Thank you
so much.
--
Regards
YM



"Spiky" wrote:

Spiky -

Thanks for the response, but is here is the part where i need the help. How
do i do that? (recording the macros and the button?).

Thanks for the help.
--
Regards
YM


Oh, sure. I like to use the Visual Basic toolbar when recording for
ease of use. So open that up by right-clicking on a toolbar and
selecting it from the menu. I have Excel 2003, so all this applies to
that version and most earlier versions.

First, plan it out. Make sure you know each step you want so you can
do them easily. Don't do anything extra, or it will record that, too.
The one we've discussed is very short, so this shouldn't be a big
deal. If you want it to open both files for you, make sure they are
both closed before you start recording. If you want to have the one
file open and run the macro from there (I mean in the future), then
open that file and be looking at it before starting.

Second, you may need to adjust your Macro Security if you have never
used any before. I think that is a button on the toolbar, or it is in
the menu: Tools|Macro|Security. Choose medium security, probably. If
you have XL2007, there is a completely different way to do this.

Record:
1) Click the Record button on the toolbar. (red dot)
2) Give the macro a name and place to save. I don't think there can be
spaces in the name, so use underscore if you want a space. If you want
it just for your computer, choose Store in Personal Macro Workbook. If
you want it attached to the file so it can be used elsewhere, choose
This Workbook.
3) Do everything you need to do. Open file, hit F9 (calculate), save,
etc. Speed doesn't matter, but do it in the right order and don't miss
anything or add anything you don't want.
4) Click the Stop button. (same button, now a square)
5) If you store it in Personal Macro Workbook, Excel may ask to save
that when you quit Excel next. Be sure to say Yes.

Make a toolbar button (if you store in Personal Macro Workbook):
1) Right-click a toolbar and select Customize from the bottom of the
menu. Go to the Commands tab. In the first pane, select Macros. In the
second pane, drag the Custom Button to wherever you would like it on a
toolbar. Don't close the Customize window, leave it open for now.
2) Right-click on your new button and select Assign Macro. Choose your
new macro from the list.
3) You can also right-click and change the name of the button (you'll
see the name in tooltips in the future), change the picture on the
button, etc. if you like.
4) Now close Customize window.

Make a button in a file (if you store the macro in one of the files):
1) Open the Drawing toolbar by right-clicking on any toolbar.
2) Click on Text Box toolbar button. Draw a text box where you want
your macro button to be.
3) Type in whatever text you want.
4) Right-click on the text box. You may have to point at the very edge
of it to right-click. Select Assign Macro from the menu and choose
your macro.
5) Format the box however you want. Background color is very helpful,
Center-orienting the text makes sense, adjust the size, I like shadows
or 3D effect to make it actually look like a button, etc. (right-click
and select Format)

Thank you Gord, it works, it is amaizing all you can do with excel.

Thanks again.
--
Regards
YM



"Gord Dibben" wrote:

ToolsMacroRecord new macro.

ViewToolbarsForms.

Click on the button icon, draw a button and assign the macro to that button.


Gord Dibben MS Excel MVP

On Mon, 23 Jun 2008 21:29:01 -0700, Yitzhack
wrote:

Spiky -

Thanks for the response, but is here is the part where i need the help. How
do i do that? (recording the macros and the button?).

Thanks for the help.