copy workbook and update
 

Hi

I have workbook with approx 20 sheets

I want a copy of this workbook, and when I update the origonal, this new
workbook will update

Is this possible ?

regards

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200902/1

copy workbook and update
 

Hi,
You can create a macro that when closing the workbook will create a copy of
it, need help with the macro just let me know

"BNT1 via OfficeKB.com" wrote:

Hi

I have workbook with approx 20 sheets

I want a copy of this workbook, and when I update the origonal, this new
workbook will update

Is this possible ?

regards

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200902/1

copy workbook and update
 

The easiest way is to use SaveCopyAs to create a copy of the workbook
as it is at some point in time. Even if the original workbook is
closed without saving changes, the workbook created with SaveCopyAs
will have the changes. E.g.,


Sub SaveCopy()
Dim FName As String
Dim N As Long
If ActiveWorkbook.Path = vbNullString Then
Exit Sub
End If
FName = ActiveWorkbook.FullName
N = InStrRev(FName, ".")
FName = Left(FName, N - 1) & "_COPY" & Mid(FName, N)
On Error Resume Next
Kill FName 'delete existing copy file
On Error GoTo 0
ActiveWorkbook.SaveCopyAs Filename:=FName
End Sub

You can automate this my putting the following code in the same code
module as SaveCopy:

Sub Auto_Close()
SaveCopy
End Sub

Thus, you can save a copy whenever you want, and automatically save a
copy with the workbook is closed.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 03 Feb 2009 18:49:40 GMT, "BNT1 via OfficeKB.com" <u19326@uwe
wrote:

Hi

I have workbook with approx 20 sheets

I want a copy of this workbook, and when I update the origonal, this new
workbook will update

Is this possible ?

regards

copy workbook and update
 

thanks for the speed response

One file would only be edited by myself, this would be the secure data, and
the other file would be able to be edited by another person, after my update.


Not sure if this will work for me?

Any ideas???



Eduardo wrote:
Hi,
You can create a macro that when closing the workbook will create a copy of
it, need help with the macro just let me know

Hi

[quoted text clipped - 6 lines]

regards

--
Message posted via http://www.officekb.com

copy workbook and update
 

thanks chip

Tried this but it does overide the copy when i run macro, which i was trying
to avoid

Put simply, once copy new worksheet created, any new info put into origonal
would transfer to other workbook. That new workbook could be amend at will
for non essential data and not effect original data?

Possible??

Chip Pearson wrote:
The easiest way is to use SaveCopyAs to create a copy of the workbook
as it is at some point in time. Even if the original workbook is
closed without saving changes, the workbook created with SaveCopyAs
will have the changes. E.g.,

Sub SaveCopy()
Dim FName As String
Dim N As Long
If ActiveWorkbook.Path = vbNullString Then
Exit Sub
End If
FName = ActiveWorkbook.FullName
N = InStrRev(FName, ".")
FName = Left(FName, N - 1) & "_COPY" & Mid(FName, N)
On Error Resume Next
Kill FName 'delete existing copy file
On Error GoTo 0
ActiveWorkbook.SaveCopyAs Filename:=FName
End Sub

You can automate this my putting the following code in the same code
module as SaveCopy:

Sub Auto_Close()
SaveCopy
End Sub

Thus, you can save a copy whenever you want, and automatically save a
copy with the workbook is closed.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

Hi

[quoted text clipped - 6 lines]

regards

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200902/1