copy workbook and update

  • Thread starter BNT1 via OfficeKB.com
  • Start date
B

BNT1 via OfficeKB.com

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
 
E

Eduardo

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
 
C

Chip Pearson

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)
 
B

BNT1 via OfficeKB.com

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???


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
Click to expand...
[quoted text clipped - 6 lines]
regards
Click to expand...
Click to expand...
 
B

BNT1 via OfficeKB.com

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 said:
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
Click to expand...
[quoted text clipped - 6 lines]
regards
Click to expand...
Click to expand...
 
Top