I figured out my coding error but when I run the macro it says "subscript out
of range". Also, how do I code for "auto_open"? Thank you.

"Duncan_J" wrote:

Try this...
Just make sure you change the path to match where your file in located...
You'll have to change it in 2 places... After that you can save the macro as
Auto_Open and it will actomatically run when woorkbook2 is opened.

Sub Copy_Worksheets()
'
' Copy_Worksheets Macro
' Macro recorded 7/5/2005 by DJ.
'

'
'Path -where your files in located.
Workbooks.Open Filename:="C:\Myfile\workbook1.xls"
Windows("workbook2.xls").Activate
Sheets("Sheet2").Select
Cells.Select
Selection.Copy
Windows("workbook1.xls").Activate
Sheets("Sheet2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows("workbook2.xls").Activate
Sheets("Sheet3").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("workbook1.xls").Activate
Sheets("Sheet3").Select
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Make sure to change this for the path
ActiveWorkbook.SaveAs Filename:="C:\Myfile\workbook1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
Range("A1").Select
End Sub

"Jane" wrote:

I have worksheet2 and worksheet 3 in a workbook2 in which I want to copy
those worksheets into Workbook1 as worksheet2 and worksheet3. I want to copy
coments, values and formats. I know how to do this manually be I'd like it
to be automatic everytime I update workbook2. Also, does workbook1 have to
be open for this to update or can it update and save without having to
manually open, save and close workbook1?

Any suggestions are appreciated. Thank you.