Saving Data

C

ceplane

I have triied to use the following code to save entered data. Howeve
it also deletes all the formulas in cells not used yet. I'm triing t
save data as it is entered. Once the data is entered the formula i
not need for that cell. However, future data is depended on th
formula in other cells not used yet. ??????


Sheet2.Activate
Set rng = Range("A3:E366")
If rng > 0 Then
rng.Formula = rng.Value
Else
rng.Formula = rng.Formula
End If


Thank you again
 
S

Simon Lloyd

You could try Activesheet.Save....i'm pretty new to this so dont kno
how it would work but for it to work when a change is made on a shee
it would have to e in Private Sub Worksheet_sheetselection_change.

HTH

SImo
 
G

Gord Dibben

ceplane

Sheets("Sheet6").Activate
Set rng = Range("A3:E366")
For Each rcel In rng
If rcel.Value > 0 Then
rcel.Formula = rcel.Value
Else
rcel.Formula = rcel.Formula
End If
Next rcel


Gord Dibben Excel MVP
 
C

ceplane

Thank you for the ideas.
But, she hangs up on

If rcel.Value > 0 Then

Doesn't look much different then what I triied before.
Sounds simple I can't get it to work.

The active sheet save didn't work either. ?????
 
G

Gord Dibben

What is "hangs up"?

Looks much different from your original code and changes formulas to values if
they return something. Leaves formulas as is if they return nothing.

As far as saving the Activesheet, I don't see that anywhere in your original
code.

Here is a simple Macro for saving the active worksheet
with the sheet name as the file name.

Sub SaveSheet()
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name, _
FileFormat:=xlNormal
End Sub

Gord
 
C

ceplane

Gord,
When the program runs, I get an error message "Runtime Error 13 Typ
mismatch" and this line is highlighted.

If rcel.Value > 0 Then

I don't know if it is the cell format that is causing the problem o
not.

Thanks for your support.
Ric
 
Top