automatic copy worksheet to anotehr workbook

[John Keith]

Every time that I open the file "temperature.xls" I would like to have
the worksheet "survey" contained "raw_data.xls" copied in to the
worksheet "baseline" (contained in "temperature.xls"). Both files are
in the same directory. I've thought about having formulas in each cell
in the target worksheet but this will not copy the formats which I
would like to maintain. I think a macro can probably do this and have
tried to record a macro (shown below with different file names to
test):


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/15/2007 by John Keith
'

'
Windows("source.xls").Activate
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
ActiveWorkbook.Close
ChDir "C:\Copy"
Workbooks.Open Filename:= _
"C:\Copy\source.xls"
Sheets("Sheet1").Select
Sheets("Sheet1").Move Before:=Workbooks("target.xls").Sheets(1)
End Sub

But using the record process I am only able to get the worksheet
inserted in fromt of Sheet1 rather than replacing sheet1. What do I
need to change in the code to get it to replace the sheet rather than
inserting in front of it?

Also, it appears with the above code the source workbook does not have
to be open to copy the worksheet I'm interested in. Is that correct?

Do I have to do anything to automatically invoke the macro when the
file is opened?

And finally, can the window that asks whether the macro should be
disabled or enabled be removed so that it is not seen when the file is
opened?

Thanks for the advice!


John Keith
(e-mail address removed)

 

[John Keith]

I've learned how to name my macro Autpen to get it to run when I
open the spreradsheet so I've learned that technique.

But I'm still seeking an answer on how to replace the target worksheet
rather than inserting in front of the first worksheet. Any
suggestions?

But using the record process I am only able to get the worksheet
inserted in fromt of Sheet1 rather than replacing sheet1. What do I
need to change in the code to get it to replace the sheet rather than
inserting in front of it?

John Keith
(e-mail address removed)