keeping worksheets from autoupdating

[viper]

Hi,

Is there a way to prevent one sheet in a workbook from updating when another one does?
What I mean is, I have workbook that contains 12 sheets(one for each month) I use the same formula for pulling the data from the other workbook for each sheet.
What I need is a way for say worksheet 2 to stay the way it is when worksheet 3 is updated. Right now all the sheets contain the same data because they use the same formula. When we are done with sheet 2 and move on to sheet 3 I need for sheet 2 to stay the way it was. I hope I explained this okay.

[brettvba]

What code are you using that would be the dependant on whether you can stop one from updating?

[Ricky Morris]

You would need to convert the formulas in the sheets you don't want to be updated to values. If you want some code that will convert linked formula on a specific sheet to values let me know.

[Qroozn]

there is no way to set xlcalculation as different in each sheets. and if you have two workbooks open.. they cant be different either. the xlcalculation is derived from the first worksheet opened. all sheets subsequent to that will have the same xlcalculation established.

'just some knowledge for you.

[Nimrod]

When I want to restrict which sheets are updated I change the sheets to manual updates.
This can be done through the Tools...options..Calculation Tab.
This tab allows you to change between auto and manaul updates. Once set to manual you can either manual update all sheets with "F9" or just selected sheet with the "calc sheet" command button.

[Qroozn]

NIMROD. I think you have solved it.

instead of us trying to not update one sheet, you worked on updating just the necessary ones.

[Nimrod]

[viper]

Thank you for your suggestions.

Ricky, yes I'd like to learn the code.

Nimrod, I'll set my sheets up as you suggested.

thanks again,

[Ricky Morris]

Here you go. This should convert the links to values on the sheet from which the macro is executed.

Sub Remove_Links()
Dim x As Integer
Dim wks As Worksheet
Dim lf As Boolean
Dim lfn As Integer
Dim linked_file As String
Dim n As Names
Dim cell As Range
Dim lk As Variant
Dim wbk As Workbook
Dim curr_addr As String
Dim linked_cell As Boolean
Dim LinkedFiles() As Variant
On Error Resume Next
Application.ScreenUpdating = False
If CommandBars(1).Controls("Edit").Controls("Links...").Enabled = False Then
MsgBox "There are no links in this file.", , "Links Not Found"
Exit Sub
End If
For Each lk In ActiveWorkbook.LinkSources(xlExcelLinks)
x = x + 1
ReDim Preserve LinkedFiles(x)
For Each wbk In Workbooks
If wbk.FullName = lk Then
lk = wbk.Name
Exit For
End If
Next wbk
LinkedFiles(x) = lk
Next lk

For x = 1 To UBound(LinkedFiles)
lfn = Len(LinkedFiles(x))
lf = False
For y = lfn To 1 Step -1
If Mid(LinkedFiles(x), y, 1) = "" Then
linked_file = Left(LinkedFiles(x), y) & "["
linked_file = linked_file & Right(LinkedFiles(x), lfn - y)
lf = True
End If
If lf = True Then Exit For
Next y
If lf = False Then
linked_file = LinkedFiles(x)
End If
If WorksheetFunction.IsNumber(WorksheetFunction.Find("'", linked_file)) Then
linked_file = Left(linked_file, WorksheetFunction.Find("'", linked_file)) & "'" & Mid(linked_file, WorksheetFunction.Find("'", linked_file) + 1, 1000)
End If
Err.Clear
curr_addr = ActiveCell.Address
Cells.Find(What:=linked_file).Select
If Err.Number <> 91 Then
Call Select_Range(linked_file)
For Each cell In Selection
Application.StatusBar = "Converting... " & cell.Address
cell.Value = cell.Value
Application.StatusBar = False
Next cell
Range(curr_addr).Select
End If
Next x
Application.StatusBar = False
End Sub

Sub Select_Range(linked_file)
Dim Linked_Cells() As String
Dim Link_Range As Range
Dim x As Integer
On Error Resume Next
Cells.Find(What:=linked_file).Activate
First_Cell = ActiveCell.Address
Do Until Next_Cell = First_Cell
Cells.FindNext(After:=ActiveCell).Activate
If ActiveCell.HasFormula Then
Next_Cell = ActiveCell.Address
ReDim Preserve Linked_Cells(x)
Linked_Cells(x) = Next_Cell
If x = 0 Then
Set Link_Range = Range(Linked_Cells(0))
Else
Set Link_Range = Application.Union(Link_Range, Range(Linked_Cells(x)))
End If
x = x + 1
End If
Loop
Link_Range.Select
End Sub


_________________
It's never too late to learn something new.

Ricky

[ This Message was edited by: Ricky Morris on 2002-05-06 22:31 ]

[ This Message was edited by: Ricky Morris on 2002-05-08 09:20 ]

[Nimrod]

I don't understand why you want to remove the links if you may want to update again later ? Why not just disable the update until needed ? See my posting above for details.