Hi

I have a workbook that contains many worksheets that have been generated from a column of data contained in work sheets in the same workbook, here is the code:
Sub AddSheetWithNameCheckIfExists()
    Dim ws As Worksheet
    Dim newSheetName As String
    LastRow = Range("q" & Rows.Count).End(xlUp).Row
    For Each cell In Sheets(1).Range("q13:q" & LastRow)
        newSheetName = cell.Value
        For Each ws In Worksheets
            If ws.Name = newSheetName Or newSheetName = "" Or IsNumeric(newSheetName) Then
                 MsgBox "Sheet already exists or name " & ws.Name & " is invalid", vbInformation
                GoTo NEXT_WS
            End If
        Next
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        Worksheets(Worksheets.Count).Name = newSheetName
        Application.Goto Sheets(1).Cells(1, 1)
NEXT_WS:
    Next cell
End Sub
Most of the data in the cells is the same on everysheet but there are some that change by referencing data on sheet1, with this formula (and with a great deal of help from Brokenbiscuit on this forum)
="DIST="&VLOOKUP(VALUE(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,3)),Sheet1!N:P,3,FALSE.

This works great while the data is in the worksheet, However when I export the sheets as a text file the resulting text file has #VALUE! instead of the result of the formula. Here is the code I am using for creating text files from each of the worksheets:
Sub SaveSheetAsTXT()
'Save Each Sheet As Seperate File


Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets
Wks.Copy 'to a new workbook
With ActiveSheet 'the new sheet in the new workbook
.Parent.SaveAs Filename:="N:\LIVE PROJECTS\MARLEY TUNNEL\gauging files\export\" & .Name & ".txt", _
FileFormat:=xlTextWindows
.Parent.Close savechanges:=False
End With
Next Wks

End Sub
Any Ideas on how I can keep the formula result in the exported text files?

Tim