automatically copy values of worksheet into new workbook

[Guest]

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.

 

[Guest]

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

 

[Guest]

Thank you for your help. If I may ask a few other questions. Can I put the
code in each sheet that I want to copy to a new workbook? I have quite a few
that need to go into different new workbooks. Also, How do I save the code
as 'Auto-Open'? Below I put the property path name but when I ran the code I
got a syntax error. Do you see a problem with what is below? Thank you.

ActiveWorkbook.SaveAs Filename:="C:\Myfile\workbook1.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _, CreateBackup:=False

 

[Guest]

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.

 

[Guest]

Hi Jane,
Actually this is pretty easy... All you have to do is Open up the workbook
you want to have the code in. Once you open it, record a macro. Open up the
work book you want to paste to. Copy your data from sheet 2 and paste special
value... Just like you would do it manually.. Just do that process for each
workbook you want to have auto copied... Stop your macro when you done.. Run
it to see if everything is working the way it should... Now remember, close
the woorkbook you copied data to cause your macro is going to open it for you
and it won't work if the workbook is already open...

Anyway, have fun...