Automation Error : The Object Invoked Has Disconnected From Its Clients !!

[donna.gough]

Folkes, I am trying to solve this problem. Searching this groups seems
to come up with 2 possible problems. Firstly corrupted sheet
(investigated this one and am sure that this is not the problem).
Secondly I am not releasing a reference to something before trying to
access again.

I am copy 2 sheets (containing charts and data with links to another
file) from one workbook to a new workbook, unprotecting each sheet and
copying /paste special values (to remove the links) and then protecting
each sheet in a For/Next loop.

I get the automation error when doing the paste special on the second
run through.

Here is my code....
MyResults(1) = "Tab1"
MyResults(2) = "Tab2"

Sheets(MyResults).Select
Sheets(MyResults(UBound(MyResults))).Activate
Sheets(MyResults).Copy
For Each s In ActiveWorkbook.Sheets
s.Activate
ActiveSheet.Unprotect
ActiveSheet.Cells.Select
Range("AX1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= False, Transpose:=False
Range("AX1").Select
s.Protect
Next s

Can anybody explain to me where I am going wrong and how to address the
problem please. Thanking you all (again) in advance.
Donna

 

[keepITcool]

i cant reproduce your error,
did a rewrite anyway

note i use typed variables.
note i use worksheets rather than sheets
note i avoid copy/paste use value=value instead

i return and ungroup the original sheets
as per your code:
i assume the protection is with a blank password ?




Sub hmm()
Dim myresults(1 To 2) As String
Dim wb As Workbook
Dim ws As Worksheet

myresults(1) = "sheet1"
myresults(2) = "sheet2"
Set wb = ActiveWorkbook

wb.Worksheets(myresults).Copy
For Each ws In ActiveWorkbook.Worksheets
With ws
.Unprotect
.UsedRange.Value = .UsedRange.Value
.Protect
.Activate
.Range("AX1").Select
End With
Next ws

wb.Activate
wb.Worksheets(myresults(UBound(myresults))).Select
End Sub

 

[donna.gough]

KeepITCool,
Thanks for the quick response.
I have used your code to the letter but still I get a run time error
but a different one this time.
When it runs through the For/Next for the 2nd time, it errors on the
..usedrange.value line with the runtime error of.....

Method 'Value' of object 'Range' Failed

...... any ideas why.

Donna

 

[donna.gough]

Furthing to my last post....
It errors out. I press 'End'. But investigating the 2nd sheet it has
actually done the .UsedRange.Value line as the links are not there and
you can confirm this as the edit menu links is greyed out.
Donna