Replacing Formulae With Values.....Continuation

[donna.gough]

I'm posting the complete macro code in a hope somebody can spot
something that may be wrong. For those of you that have not been
keeping track of my previous post over the last few days; I am copying
2 sheets from one file into a new file and then removing all the
formulae by replacing them with the cell value. I am doing this to
remove links that I have present. The problem I have though is that it
errors out during the last usedrange.formula=usedrange.value. If I
have copied one sheet it fails on that one. If I have copied 2 sheets,
then it does the first one but fails on the second!. The Runtime error
that pops up happens on the last run through of the '*'d line with the
error message.....
Mehtod 'Formula' of object 'Range' failed
.......Here's my code....

Option Explicit

Sub Actual1()

Dim FName As String
Dim i As Integer
Dim s, w
ReDim MyResults(1 To 100)
Dim iArea As Range


''''''''''''''''''''''''''
' Selects The Chart File '
''''''''''''''''''''''''''
For Each w In Workbooks
If InStr(w.Name, "Charts") Then
FName = w.Name
Exit For
End If
Next w

If FName = "" Then
MsgBox ("You Need A Chart File Open.")
GoTo End1:
Else
Workbooks(FName).Activate
End If

''''''''''''''''''''''''''''''''
' These Are The Sheets To Copy '
''''''''''''''''''''''''''''''''
MyResults(1) = "A3RH"
MyResults(2) = "C6LH"
ReDim Preserve MyResults(1 To 2)

Workbooks(FName).Activate
Sheets(MyResults(UBound(MyResults))).Activate
Sheets(MyResults).Copy
Worksheets.Add after:=Worksheets(Worksheets.Count)

ChDrive "I"
ChDir "I:\Data\Temp\Copy Chart"
ActiveWorkbook.SaveAs Filename:="Copy Chart.xls"
Application.CutCopyMode = False

''''''''''''''''''''''''''''''''''''''''
' Removes All Formulae And Hence Links '
''''''''''''''''''''''''''''''''''''''''
For Each s In ActiveWorkbook.Sheets
s.Activate
s.Unprotect
Cells.Select
Selection.MergeCells = False
Columns("AZ").ColumnWidth = 17.75
Range("AX1").Select
s.UsedRange.Formula = s.UsedRange.Value
s.Protect
Next s

End1:
End Sub

.......Any ideas? And thanks to those guys that have kept posting to my
previous thread over the past few days.

 

[donna.gough]

the '*'d line being....
s.UsedRange.Formula = s.UsedRange.

 

[ben]

This has worked for me in the past
s.usedrange.value = s.usedrange.value

 

[donna.gough]

I've tried that as well. It just fails with the message Method 'Value'
of object 'Range' failed instead.
I split it up into FOR EACH iArea in ActiveSheet.UsedRange.Formula and
stepped through each iArea and it will do them all until it gets to
last iArea on the last copied sheet it encounters. Why !

I telll you what....."lose your mind, free your life"....I'm as free as
a bird at the moment.
It must be something silly, but I can't spot anything. The thing that
is bugging me is if it's one sheet I've copied then it fails on that
sheet but if I copy another along with it, it will then change the 1st
sheet that it failed on before but fail on the 2nd one.

 

[Tom Ogilvy]

I ran your code and it ran fine for me.

Debugging would probably require access to the files causing the fault.

 

[Toppers]

Hi,
Assuming I emulated your requirement correctly, your code worked OK
for me. (XL2003).

I had a workbook called "Charts", with your worksheets "A3RH", "C6LH" and
both sheets contained simple formulae e.g SUM, AVERAGE, MAX & MIN. Both
sheets referenced data on the other.

I (your code!) created "Copy charts" workbook with the worksheets above and
all formulae were converted to values.

Both s.UsedRange.Formula = s.UsedRange.Value and s.UsedRange.Value =
s.UsedRange.Value worked.

Sorry!

 

[donna.gough]

Some of the cells have the following formula.....
=IF(OR(C51="",C52=""),NA(),ABS(C52-C51))
.....with the contents either being a number or #N/A.
Does this cause the problem? I don't think so, because if I split the
replacing up using the FOR EACH iArea method, then it comes across the
same formula and replacing them with the value in the same sheet. It
seems to me that the last iArea contains something extra..perhaps
something that isn't a cell? I do have charts and commandbuttons on
the sheets also. Do you think it is selecting one of those aswell? I
can't see that it does. Using iArea.Select before the Formula=Value
line, it seems to only have cells highlighted, but in theory can the
command select something else?

 

[ben]

there's a possibility it's failing because of a corrupt cell or bad value
somewhere does it always fail on the same sheet, no matter when you try
copying it?

 

[ben]

Donna,

Have you tried copying the usedrange and the running the
pastespecial using the xlvalues command to a blank worksheet?

 

[Tom Ogilvy]

I had a forms checkbox and a chart on each of the test sheets and no
problem. I added command buttons and formulas returning #N/A. No problem.

 

[donna.gough]

i don't know about a corrupt cell/value...i am beginning to think along
those lines.
All the copied sheets have the same format with more or less the same
formulae in and the same links.
It doesn't fail on the copy but then fails on the replacing formula
with value line.
If I copy sheet named "A3RH" only then it fails on that sheet, if I
copy "A3LH" and "C6LH" then it will replace the formulae on "A3RH" but
will then fail on "C6LH".....see why I am stumped!
I'm trying to think if there may be something corrupt, but if it will
replace all the formulae on ths sheet aslong as it's not the last sheet
that was copied...then surely there can't be something corrupt on the
original sheets. If there is something corrupt then it must be
something in this macro doing it....yes or no?

 

[Tom Ogilvy]

Your macro doesn't do anything to the original sheets.

If you want to zip up the workbook and send it to me, I can see if I can
reproduce the problem

(e-mail address removed)

 

[donna.gough]

using .....
s.UsedRange.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
.......again it will do the first sheet but on the last sheet comes up
with a different error while trying to execute the paste special
line.....
Automation Error
The object invoked has disconnected from it's client
......I don't know what this error indicates. Any ideas.

 

[donna.gough]

Tom,

I can't see all your e-mail address. Where ever i look I can only
find...
twogi...@msn.
I think my full email address is available (because I can't find out
where to hide it!) so could you mail me so I have your address please.
Thank you.

 

[Dave Peterson]

twogilvy (at) msn.com

Tom,

I can't see all your e-mail address. Where ever i look I can only
find...
twogi...@msn.
I think my full email address is available (because I can't find out
where to hide it!) so could you mail me so I have your address please.
Thank you.

 

[donna.gough]

Right then.....One Step forward....that as usual results in a step
backwards!
I have a file with 23 sheets, I copy 2 of them to a new file and then
try and remove all the formulae from the 2 copied sheets in the new
file. This results in it erroring while trying to replace the
formulae. So I tried running the loop that replaces the formulae on
the original file that contains all 23 sheets....success, no problems
what so ever. So, in defeat I have rejiged my macro to SaveAs the full
file and then delete the sheets I don't need and then run the loop to
remove all the formulae and success...I'm happy. BUT, it will always
bug me why it would fail to replace the formulae when the sheets were
copied!... I think it may have corrupted/caused problems to specific
copied sheets rather than what I originally thought, that it seemed to
fail on the last of the copied sheets.

Thanks for all your input and if you can suggest why my copy method
seems unstable then please let me know.
Donna