Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Auto Backup

  Asked By: Donna    Date: Aug 09    Category: MS Office    Views: 754
  

I'm new to VBA, but want to create a macro that will allow me to auto
copy the workbook that I'm using, in to a different folder - an auto
backup if you will. But the workbook contains alot of linked formulas
and I need to ensure that the values of the copy cannot be changed;
similar to as though I had used 'Paste Special'\Values.

I don't know where to start.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Lula Woods     Answered On: Aug 09

Well you could create  a new workbook  and actually populate it with Paste Special
/ Values.

Or you could simply lock the copy  down so that it will be read-only.

Not too sure what you mean by "linked formulas". Do you mean that they are
linked to another workbook that might change even after you made the copy? If
so, locking it down won't do the job, you need to remove the formulas with
copy/values.

However, if the formulas are simply referring to other cells in the same
workbook, then it's the cells without the formulas that you really need to lock
down. If they can't change, then the values  returned by the formulas won't
change.

 
Answer #2    Answered By: Fayina Bonkob     Answered On: Aug 09

There are several workbooks, all linked  over an intranet. Different
users enter data from their own area in to their own workbook, which
provides data to other workbooks on the system.

Each week the workbooks are manually backed-up in to an archive
folder and refreshed to allow the entry of new data, but this has had
its problems, due to varying degrees of IT competence resulting in
data loss.

I'd like to create  a routine that would be initiated through the
Click event of a command button, to auto  backup each workbook  and
refresh for new data entry.

 
Answer #3    Answered By: Fabian Ferrrari     Answered On: Aug 09

OK. Copy/paste-special/values is probably the way to go.

There might be added complexities, e.g. if your workbook  has multiple
worksheets and/or is nicely formatted, however, the basic principle can
remain the same, and similar to doing it by hand.

The easy way to eliminate formulas and links from a worksheet manually is to
select the full extent of its cells, then copy, then paste-special/values
over the same location. This seems to work fine in all cases. And it lends
itself to being recorded as a macro.

I just popped values  in a couple of cells of an empty sheet and recorded:
ctrl-home, ctrl-shift-end, copy  (ctrl-c), paste-special/values (alt-e, s,
v). The code recorded (Excel 2003) is nice and general:

Option Explicit

Sub Macro1()
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End Sub

I.e. the macro  didn't record absolute positioning for the bottom of the
data, which makes it usable on any sheet.

If your workbook only contains a single sheet (or exactly the same sheets
every time), it is possibly easiest to create  a new empty workbook, then
copy all the sheets to it (so that you get column widths and formatting),
then do a copy/paste-special for each sheet. You should be able to record a
macro of yourself doing all of this. Then you can simply tie it to a button
or hot-key sequence for use later.

Of course, you'll probably want to tailor the code a bit once you have
recorded it, but the recording facility should get you well down the road.

 
Didn't find what you were looking for? Find more on Auto Backup Or get search suggestion and latest updates.




Tagged: