Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: keeping worksheets from autoupdating

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


    I appreciate the help from everyone at Mr. Excel.

    viper

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    It's never too late to learn something new.

    Ricky

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    NIMROD. I think you have solved it.

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

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

    I appreciate the help from everyone at Mr. Excel.

    viper

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •