'
' LocalToGlobal Macro
' Macro recorded 03.02.2006 by FUriCK
'
   Dim wsh
   Dim Filename
   Dim newPath
   maxColumns = 1000
   maxRows = 1000
   
   Dim OrigWB
   Set OrigWB = ActiveWorkbook
   
   newPath = OrigWB.Path + "\"
   
   Dim OrigSheets
   Set OrigSheets = OrigWB.Sheets
   
   For Each wsh In OrigSheets
      'Save the all sheets to different files:
      FN = newPath + wsh.Name + ".xls"
      wsh.Select
      wsh.Copy
      ActiveWorkbook.SaveAs Filename:=FN, _
       FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
       ReadOnlyRecommended:=False, CreateBackup:=False
      'wsh.SaveAs Filename
      OrigWB.Activate
   Next wsh
   
   
   Dim cellVal As String
   For Each wsh In OrigSheets
      'replace the other sheets names with global links:
      For Each other_wsh In OrigSheets
      If other_wsh.Name <> wsh.Name Then
         For rw = 1 To wsh.UsedRange.Rows.Count
               For cl = 1 To wsh.UsedRange.Columns.Count
                   'If wsh.Cells(rw, cl).HasFormula Then
                       cellVal = wsh.Cells(rw, cl).FormulaLocal
                       If InStr("=", cellVal) = 0 Then
                           cellVal = Replace(cellVal, other_wsh.Name + "!", "#TOREPLACE#")
                           cellVal = Replace(cellVal, "#TOREPLACE#", "[" + other_wsh.Name + ".xls]" + other_wsh.Name + "!")
                           wsh.Cells(rw, cl).Formula = cellVal
                       End If
                   'End If
               Next cl
         Next rw
      End If
      Next other_wsh
   Next wsh

   For Each wb In OrigWB.Application.Workbooks
   If wb.Name <> OrigWB.Name Then
       wb.Close SaveChanges:=True
   End If
   Next wb

   For Each wsh In OrigSheets
      'Save the all sheets to different files:
      FN = newPath + wsh.Name + ".xls"
      wsh.Select
      wsh.Copy
      ActiveWorkbook.SaveAs Filename:=FN, _
       FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
       ReadOnlyRecommended:=False, CreateBackup:=False
      'wsh.SaveAs Filename
      OrigWB.Activate
   Next wsh