MS Excel 97 SR-2

Question 1) Macro crashes on paste special

I can perform the macro sequence (See 'C2 = 2 below) (copy entire sheet
and paste special {values}) manually within the Excel worksheet but
when I either execute or step through the macro lines it always crashes
with the familiar ESOD message

" Excel.exe has generated errors and will be closed by Windows.
You need to restart the program. "

This happens on the line that performs
Edit - Paste Special {Values}

The only thing that I can think is that there is insufficient time
before the next macro line executes (physically the paste special takes
in the order of seconds as there is a fair amount of content), however
why this should be a problem while stepping through line by line in
debug mode is beyond me.


Question 2) Can the first copy specify an absolute reference to Book1

The macro code is interesting as two lines are generated by an excel
sheet, that step through a whole host of sheets that are copied out to
an external workbook and in each copy all active cells replaced with
their values. All the subsequent copies can refer to Book1 which holds
these copies, but how can I get the first copy to use this as the
target work book?

Question 3) Is this really the best way of achieving an abridged,
tamper-proof, readonly copy of a working excel book?

What I actually want to do is export a whole bunch of result sheets
(which fetch results off a master analysis sheet) but
a) not need to export the big analysis sheet and
b) not allow the end user to overtly, or inadvertently, change the
results in the sheet.

I have tried the option of locking cell contents on each sheet, but
this appears to require a password that must be entered twice
(interactively) for each sheet - too much pain for all the sheets that
must be exported. Is there not a way for the macro to provide a
password when locking the sheet? Would locking cell contents avoid the
problem that cell formulae point to non-existent sheets (if I switched
off auto calculation) (which would then avoid the need to replace
active cells with just the calculated values)?

I have experimented with the lock workbook option, this is quite neat
as it locks the windows, but doesn't seem to have anything to do with
content.


'C2 = 2
Windows("MasterEMSDB.xls:1").Activate
Sheets("ABS Test Bench ").Select '
=INDEX(CopySheetList!C:C,C2,1)
Sheets("ABS Test Bench ").Copy '
=INDEX(CopySheetList!D:D,C2,1)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

'C12 = 3
Windows("MasterEMSDB.xls:1").Activate
Sheets("ABS").Select
Sheets("ABS").Copy Befo=Workbooks("Book1").Sheets(1) '
=INDEX(CopySheetList!D:D,C12,1)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Questions 1 and 2)

Dim bk as Workbook
Workbooks"MasterEMSDB.xls") _
.Sheets("ABS Test Bench ").Copy
set bk = ActiveWorkbook
ActiveSheet.UsedRange.Formula = _
Activesheet.usedRange.Value
ActiveSheet.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True


Workbooks.("MasterEMSDB.xls:1") _
.Sheets("ABS").Copy Befo=bk.Sheets(1)
ActiveSheet.UsedRange.Formula = _
Activesheet.usedRange.Value
ActiveSheet.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

An alternate Method

Dim sh as Worksheet
Workbooks.("MasterEMSDB.xls:1") _
.Worksheets(Array("ABS Test Bench", _
"ABS").copy
set bk = ActiveWorkbook
for each sh in bk.Worksheets
With sh
.UsedRange.Formula = UsedRange.Value
.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
end with
Next

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
MS Excel 97 SR-2

Question 1) Macro crashes on paste special

I can perform the macro sequence (See 'C2 = 2 below) (copy entire sheet
and paste special {values}) manually within the Excel worksheet but
when I either execute or step through the macro lines it always crashes
with the familiar ESOD message

" Excel.exe has generated errors and will be closed by Windows.
You need to restart the program. "

This happens on the line that performs
Edit - Paste Special {Values}

The only thing that I can think is that there is insufficient time
before the next macro line executes (physically the paste special takes
in the order of seconds as there is a fair amount of content), however
why this should be a problem while stepping through line by line in
debug mode is beyond me.


Question 2) Can the first copy specify an absolute reference to Book1

The macro code is interesting as two lines are generated by an excel
sheet, that step through a whole host of sheets that are copied out to
an external workbook and in each copy all active cells replaced with
their values. All the subsequent copies can refer to Book1 which holds
these copies, but how can I get the first copy to use this as the
target work book?

Question 3) Is this really the best way of achieving an abridged,
tamper-proof, readonly copy of a working excel book?

What I actually want to do is export a whole bunch of result sheets
(which fetch results off a master analysis sheet) but
a) not need to export the big analysis sheet and
b) not allow the end user to overtly, or inadvertently, change the
results in the sheet.

I have tried the option of locking cell contents on each sheet, but
this appears to require a password that must be entered twice
(interactively) for each sheet - too much pain for all the sheets that
must be exported. Is there not a way for the macro to provide a
password when locking the sheet? Would locking cell contents avoid the
problem that cell formulae point to non-existent sheets (if I switched
off auto calculation) (which would then avoid the need to replace
active cells with just the calculated values)?

I have experimented with the lock workbook option, this is quite neat
as it locks the windows, but doesn't seem to have anything to do with
content.


'C2 = 2
Windows("MasterEMSDB.xls:1").Activate
Sheets("ABS Test Bench ").Select '
=INDEX(CopySheetList!C:C,C2,1)
Sheets("ABS Test Bench ").Copy '
=INDEX(CopySheetList!D:D,C2,1)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

'C12 = 3
Windows("MasterEMSDB.xls:1").Activate
Sheets("ABS").Select
Sheets("ABS").Copy Befo=Workbooks("Book1").Sheets(1) '
=INDEX(CopySheetList!D:D,C12,1)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True