Update closed workbooks (2000 up)

[Leif]

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...
End Sub</code>

...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!

[HansV]

In the workbook containing the macro, set a reference to Microsoft Visual Basic for Applications Extensibility 5.3 (using Tools | References in the Visual Basic Editor).

You can now use code like this:

Dim wbk As Workbook
Dim strLines As String
Dim vbc As CodeModule
' Important - turn off event handling
Application.EnableEvents = False
' Open workbook
Set wbk = ...
' Use name of worksheet that contains correct Worksheet_Activate procedure
Set vbc = ThisWorkbook.VBProject.VBComponents("MySheet").Cod eModule
' Fill string variable with code
strLines = vbc.Lines(1, vbc.CountOfLines)
' Use name of worksheet to which you want to add the code
Set vbc = wbk.VBProject.VBComponents("OtherSheet').CodeModul e
' Delete any existing code
vbc.DeleteLines 1, vbc.CountOfLines
' Add code from the string variable
vbc.AddFromString strLines
' Turn on event handling again
Application.EnableEvents = True

For background, see Programming To The VBE on Chip Pearson's site.

[Leif]

Thank you! - that looks just the ticket!

It's not knowing where to look that usually trips me up... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>