Excel - auto-import worksheet on startup 1

[wtotten]

I have a Excel 2003 spreadsheet (workbook). When I open it, I would like it to open 3 other Excel files and copy them into the first file, each in it's own worksheet. Any ideas?

Bill

 

[xlbo]

And what do you have thus far / what have you tried ?

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[wtotten]

I haven't tried anything yet - that's why I'ma sking the question.

 

[GlennUK]

A starting point would be to open your first book, start macro recording, open the other books, and drag their sheets into the first book. Stop recording.




Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[wtotten]

Thanks Glenn - but how do I get the macro to automatically run when I open the workbook?

Bill

 

[Eugene99]

Bill,

Once you have stopped recording, hit "Alt F11" and go into the VBA Editor. You should have the Project Explorer on the left side of the window. To get the Project Explorer if you do not have it showing, hit "Ctrl-r". Click on the Modules folder and then click on Module1. You should see the macro that you recorded on the right side of the window. Copy everything in your macro except for the first line, which probably says "Sub Macro1 ()" and the last line that says "End Sub"

Back in the Project Explorer, you will see the Microsoft Excel Objects folder. Within this folder, you will see "This Workbook". When you double click this item in the Project Explorer, the right side of the window will be blank. In the right side blank area, Type this and hit enter:

 Private Sub Workbook_Open ()

When you hit enter, it automatically adds the last line that says "End Sub", so you will end up with this:

 Private Sub Workbook_Open ()

End Sub

Paste the text of your macro in between the two lines above.

Save your workbook, close it, and when you reopen it, it will run this macro automatically.

Hope this helps...
Eugene