Calculation Flag

[Cbrine]

OK, I'm using an automated process to produce parsed and customized sheets for a bunch of locations. Same structure, different data. The sheet are produced fine on my system, and they work fine on my system. I just recieved an email from one of the users, saying that the formula aren't updating. The same thing occurred on my bosses system with my spreadsheet(2007). After a little digging, it looks like the calculation flag is switching to manual somehow when my sheet is opened. I also tested on 2003 and encountered the same issue. I'm using 2007 to build the sheets, but I'm saving to a 2003 format. I used the Application.Calculation = xlCalculationManual when I built the sheets to speed up the processing, but I was under the mistaken impression that the calculation flag was a defaulted value that could only be change via running macro code, and was not EVEN ASSOCIATED WITH THE SHEET(It's part of the application), so how are my sheets switching the flag to manual? Is this another 2007+ Feature....FRUSTRATED. The systems where the issue is occuring are also getting a link message because of the original file missing. I know that linked files and saving 2003 format have issues, so I'm thinking this might be the culprit. Can't do anything with the sheets I have out there already, but I want to understand what happened so I can prevent it in the future.

Thanks
Cal

Code used to produce files. Useless without the structured data though, but figured I would put it up.

Dim wb As Workbook, ws As Worksheet
Dim pb As Workbook, ps As Worksheet

Dim FilePath As String
Dim cell As Range
Dim list As Worksheet

Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set list = wb.Sheets("Listing")

'Get returned data folder.
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .ButtonName = "Select"
    .Title = "Select Return Template Folder"
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "Process Cancelled"
        End
    End If
    FilePath = .SelectedItems(1)
End With
'56 is Excel 2003 save value for saveas!!!!

Application.Calculation = xlCalculationManual

For Each cell In list.Range("A2", list.Range("A65535").End(xlUp))
    
    Set pb = Workbooks.Add
    Set ps = ActiveSheet
    ps.Name = Replace(Replace(Trim(cell), "/", "-"), "'", "")
    pb.Colors = wb.Colors
    
    ws.AutoFilterMode = False
    ws.Range("A2", ws.Range("A65535").End(xlUp).Offset(0, 15)).AutoFilter 8, cell, xlOr, ""
    ws.Range("2:" & ws.Range("P65535").End(xlUp).Offset(6, 0).Row).Copy
    
    ps.Range("A1").PasteSpecial (xlPasteColumnWidths)
    ps.Range("A1").PasteSpecial (xlPasteFormats)
    ps.Range("A1").PasteSpecial (xlPasteValidation)
    ps.Range("A1").PasteSpecial (xlPasteFormulas)
    
    ps.Range("D:H").EntireColumn.Hidden = True
    ps.Range("J:J").EntireColumn.Hidden = True
    ps.Range("L:L").EntireColumn.Hidden = True
    
    ps.Range("K2", ps.Range("N65535").End(xlUp)).Copy
    ps.Range("K2").PasteSpecial xlPasteValues
      
    With ps.PageSetup
        .CenterHeader = "&""Tahoma,Bold""&14 " & ps.Name & " 2010 Confirmation"
        .LeftHeaderPicture.Filename = "N:\My Pictures\logo-Header.bmp"
        .LeftHeader = "&G"
        .LeftFooter = "&8&F"
        .RightFooter = "&8&P of &N " & Date
        .PrintTitleRows = "$1:$1"
        .CenterHorizontally = True
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesTall = False
        .FitToPagesWide = 1
        .TopMargin = 100
    End With
    
    
    ps.Protect "Password"' Not my password, changed just in case any of my co-workers stumble across this site:-)
    ps.Range("I2").Select
    
    Application.DisplayAlerts = False
    
    pb.SaveAs Replace(Replace(Replace(Trim(ps.Range("J2")) & "-" & StrConv(Trim(cell), vbProperCase) & ".xls", "'", ""), "\", "-"), "/", "-"), xlExcel8
    
    pb.Close False
    Application.DisplayAlerts = True
    ws.AutoFilterMode = False

    
Next cell
Application.Calculation = xlCalculationAutomatic

 

[Cbrine]

OK, looks like i get to answer my own question.

It looks like the grahpic that I'm uploading into the header is the issue. It was linked to a file in "My Pictures". When the distribution sheets go out, it's asking to refresh the links, and when they don't, the sheet appears to be in Manual Calculation mode. I took the sheet again, and Broke the links, saved it and sent to co-worker. With the links removed, I didn't get the link update message and the file is now in automatic calculation mode. I'm not sure why this is occurring, but the links seem to be the issue.