I have several thousand workbooks in which I need to update data due to changing legislation, working practices, QA requirements etc. Nominally, all I need to do is the range A1:I9 with a new format which I have successfully done in the past by running a macro that opens each file in turn, copies the new data from a template, then saves and closes the file.
These are production parts lists which may contain a number of worksheets, depending on which issue it is on. Typically, if a small change is made, the current worksheet is copied to the same workbook, the stated issue number changed and whatever revisions that are required made. However, we need to trap this event and mark this new worksheet as 'provisional' to prevent it being used before it has been authorised. (In theory, the production department could open the workbook in read-only mode and assume the 'top' worksheet is current.)
I have found that I can detect the copying of the workbook by adding the following code to the worksheet:
<code>Private Sub Worksheet_Activate()
If Right(ActiveSheet.Name, 1) = ")" Then
'Code to mark as provisional here...
...but of course the existing workbooks/worksheets do not contain this code.
So, help please! I think I have two choices:
1] Copy a new worksheet (including the data changes and Private Sub Worksheet_Activate() macro) to the workbook being updated. Copy all data apart from the range A1:I9 from the existing worksheet into the new worksheet, then delete the old worksheet.
Problem - the existing worksheet may contain graphics which may be anywhere on the worksheet and would have to be included in the copy.
2] Copy the new A1:I9 block from the template to the existing worksheet and add the Private Sub Worksheet_Activate() macro.
Problem - I haven't the slightest idea how to copy a macro from one worksheet to another!
Solution 2 would be preferred as it covers me against not copying something I haven't thought of, such as comments added outside the expected range!
I trust this makes sense - all comments and suggestions gratefully received!