faux versioning

[keithwins]

I would like to save/backup/version a few columns of a worksheet as it goes through a bunch of revisions. I can think two approaches: 1) a macro button that copies those few columns to the next available columns of another worksheet, and perhaps date-stamps them -- so every time a revision cycle is finished, the user punches the button and saves the columns; or 2) perhaps every time the file is saved (and those columns have changed?), it would run the same macro automatically: that's more elegant.

Anyone have any other thoughts on this, and/or code? Is there a better way to do it?

[TMS]

Or maybe use SaveCopyAs in the BeforeSave event handler to make a copy of the entire workbook? You could date and time stamp the copy file. Then you have the data in context. If you save the changed columns in the same workbook, it is open to change/corruption as it is just another worksheet. And, if anything happened to the file, you lose everything.

Regards, TMS

[Richard Buttrey]

Hi,

I'd definitely use the automated version so that version column backups are made when necessary whenever the Workbook Save event is triggered.
The trick will be deciding whether columns have changed.

One way would be to copy the relevant columns and paste them as values to a temporary sheet whenever the Workbook Open event is triggered on loading the file. Then in the temporary sheet you could have formulae which compare each cell in the temporary sheet with the equivalent cell in the original sheet. e.g

=IF(A1='MainSheet'!A1,0,1)

On this temporary sheet have a single 'Check' cell that Sums the values of the comparison cells. When the Workbook Save event is triggered then this would read the value of the Check cell and if it's not zero (indicating at least one cell has changed) then it would proceed to copy the relevant values to the next set of columns on the Version Backup sheet. The header for each set of copied and pasted columns could be the system date. i.e. the macro would use =A1=Now as a date/time stamp.

You'll need to consider what should happen if the workbook is saved more than once in a session. Do you want to keep any intermediate changes (probably not) or just the final session change.

[Richard Buttrey]

...and reflecting on TMS's comment which is a good suggestion, if you don't want to save the whole workbook, you could save a copy of the Version Backup sheet I mentioned as a separate file.

[keithwins]

Thanks for all the quick replies! I think saving within the workbook is going to be the way to go, this is a file that bounces back and forth between people and organizations, and so having other files associated with it is a recipe for disaster.

I like the idea of
1) Copy relevant columns to temp location on start/open
2) Compare those with current contents at close
3) If different: Copy to next available column on versioning worksheet, with datestamp

I think that does it. I think there's some clever way to get the last-used column in a worksheet? Anyway, I'll take a stab at this and come back with more questions. If I'm missing anything or going down a problematic road here, let me know!

Keith

[keithwins]

I'm stuck trying to cycle through the columns and rows of a given range: what I want to do is cycle through a handful of columns, and then loop down to the end of each column doing comparisons with a different column. Anyway, this is clearly not right semi-psuedo-code:

For Each TCol In Range("D8:K129").Columns
For Each Cell In TCol.Rows
'If CompareCols(OrigCols, CurrCols) Then
'Copy relevant columns & date stamp
'End If
Next
Next

[keithwins]

In thinking about this, I realize I just need to run some indices through the range limits, and use offsets. I'll hit it tomorrow and come back with some more questions. I'm still a little curious to why the above code doesn't work-ish.

K

[Richard Buttrey]

I'd avoid loops wherever you can and where standard excel functionality is available use that. Loops are inherently slow, particularly if you don't read all the data first into a VBA array and process them entirely within VBA. That's becuase Excel has to jump backwards and forwards between VBA and the Excel app at each iteration and there is a large time overhead involved.

The fastest way I know of doing the stuff you suggest is to use an Advanced Filter which will filter out the data you're interested in logging in fractions of a second.