Copying workbook contents to another workbook

[Josh Sale]

I have an XL97 - XL2003 add-in that needs to copy the entire contents of one
workbook to another workbook without effecting the source workbook. I want
to copy all of the sheets, forms, modules, class modules, etc.

One obvious solution is to just perform a Save As operation on the source
workbook. Unfortunately, if the source workbook wasn't previously saved,
then it would be at the end of this operation and that violates my need to
not effect the source workbook.

Right now, all I'm copying is sheets and I'm doing that by looping throught
he source sheets and performing a Copy operation to the target workbook.

Anybody have any suggestions on a smarter way to do this?

TIA,

josh

 

[Don Guillett]

Why can't you savecopyas and save too as I do for my backup macro.

Sub Backup() 'kept in personal.xls & assigned to toolbar button
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub

 

[Josh Sale]

Thanks Don!

I've been messing around in Excel's object model for years and had never
noticed that method.

You're my hero.

josh

 

[Don Guillett]

Oh gosh. Never been a hero before.