Possible to save versions in an Excel file?

  • Thread starter Thread starter Android
  • Start date Start date
A

Android

Hi,

I have a workbook where one sheet contains data estimates. I would like to
save a new version each month so that I can go back to see how the estimates
matched the actuals.

Is this possible in Excel, without 3rd party tools? I see that in Word you
can do this via File --> Versions....).

Android.
 
I have a workbook where one sheet contains data estimates. I would like to
save a new version each month so that I can go back to see how the estimates
matched the actuals.

Is this possible in Excel, without 3rd party tools? I see that in Word you
can do this via File --> Versions....).
Click to expand...
...

Tools > Scenarios...

Read about it in online help.
 
You could save the one worksheet as a workbook with the date as a name.

Manually open a new workbook then copy the sheet into it and save as whatever
name you want to give it.

VBA macro.....

Sub SaveSheet()
Dim fname
fname = "Version " & Format(Now, "YYYYMMDD")
Sheets("MySheet").Copy
ActiveWorkbook.SaveAs Filename:=fname
End Sub

Gord Dibben Excel MVP
 
Thanks.

First time I have looked at this. Although it seems to have a limit of
changes to 32 cells.

Android.
 
I'm not sure I quite follow. Will this not create a new file for every
version?

Android.

Gord Dibben said:
You could save the one worksheet as a workbook with the date as a name.

Manually open a new workbook then copy the sheet into it and save as whatever
name you want to give it.

VBA macro.....

Sub SaveSheet()
Dim fname
fname = "Version " & Format(Now, "YYYYMMDD")
Sheets("MySheet").Copy
ActiveWorkbook.SaveAs Filename:=fname
End Sub

Gord Dibben Excel MVP
Click to expand...
 
Yes. It will create a new file with one worksheet for every version. Thought
you wanted that.

If you just want a worksheet in the original file....

Copy the sheet then CRTL + A to select all cells the Copy/Paste
Special>Values>OK>Esc.

To copy a sheet, right-click on sheet tab and "move or copy". Check "create a
copy" and OK.

Gord Dibben Excel MVP

I'm not sure I quite follow. Will this not create a new file for every
version?

Android.
Click to expand...
 
Thanks. I was actually hoping there was an equivalent to the "version"
option in Word, which allows different versions within the same document,
because that sounded easier to manage. The "scenario" option suggested by
Harlan Grove above seems the closest to that in Excel so far.

However, given I have more than 32 cells which change, I will probably use
the multiple file option.

Thanks again.

Android.


Gord Dibben said:
Yes. It will create a new file with one worksheet for every version. Thought
you wanted that.

If you just want a worksheet in the original file....

Copy the sheet then CRTL + A to select all cells the Copy/Paste
Special>Values>OK>Esc.

To copy a sheet, right-click on sheet tab and "move or copy". Check "create a
copy" and OK.

Gord Dibben Excel MVP

Click to expand...
 
Back
Top