help Creating seperate files from existing sheets

Before I read forward I've stopped here:

TexttoFind = "Sum"
TexttoFind = "3"
TexttoFind = "4"
TexttoFind = "5"
TexttoFind = "6"
TexttoFind = "7"
TexttoFind = "8"
TexttoFind = "9"

I do not get it? What value are you expect in TexttoFind?

p.s. wrap code into CODE tag, please.

But after all these lines TexttoFind="9", not "Sum", not "3", not "4", not "5" etc.

Try like this:

Sub SplitEachWorksheet()
Dim ws As Worksheet
Dim Fpath As String
Fpath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Sum" Or ws.Name = "3" Then  'add the rest of names, please
        Dim wb As Workbook
        Set wb = ws.Application.Workbooks.Add
        ws.Copy Before:=wb.Sheets(1)
        wb.Sheets(2).Delete
        With wb.Sheets(1).UsedRange
            .Value = .Value
        End With
        wb.Sheets(1).Name = ws.Name
        wb.SaveAs Fpath & "\" & ws.Name, Excel.XlFileFormat.xlOpenXMLWorkbook
        wb.Close
        Set wb = Nothing
    End If
Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

should works.

No it is not. My bad.
There is no Sum sheetname, you want to check if Sum is in name, so this way:

Sub SplitEachWorksheet()
Dim ws As Worksheet
Dim Fpath As String
Fpath = ThisWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
    If InStr(1, ws.Name, "Sum", vbBinaryCompare) <> 0 Or _
    InStr(1, ws.Name, "3", vbBinaryCompare) <> 0 Or _
    InStr(1, ws.Name, "4", vbBinaryCompare) <> 0 Or _
    InStr(1, ws.Name, "5", vbBinaryCompare) <> 0 Or _
    InStr(1, ws.Name, "6", vbBinaryCompare) <> 0 Or _
    InStr(1, ws.Name, "7", vbBinaryCompare) <> 0 Or _
    InStr(1, ws.Name, "8", vbBinaryCompare) <> 0 Or _
    InStr(1, ws.Name, "9", vbBinaryCompare) <> 0 Then
        Debug.Print ws.Name
        Dim wb As Workbook
        Set wb = ws.Application.Workbooks.Add
        ws.Copy Before:=wb.Sheets(1)
        wb.Sheets(2).Delete
        With wb.Sheets(1).UsedRange
            .Value = .Value
        End With
        wb.Sheets(1).Name = ws.Name
        wb.SaveAs Fpath & "\" & ws.Name, Excel.XlFileFormat.xlOpenXMLWorkbook
        wb.Close
        Set wb = Nothing
    End If
Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

p.s. Could you edit your post instead of creating 3 in a row in few minutes, please?

Once more, please: Use Edit option. Do not comment your own post.

Checked attached file.
3 sheets:
Summary (should be created)
Sheet1 (won't be created)
Sheet99 (should be created)

all of them has =rand() function in cells. But created 2 files (summary and sheet99) have values instead of.

EDIT: I've got it now. After pasting into new workbook all references failed and gives errors. I'll tweak this.
EDIT2: no. it works fine. attached sample of your file. There should be another type of issue.

Did you check attached file? Have sheets been created as expected with values?

yes I checked it and it was great. exactly like that I would like my workbook to do.

should I use the code in the file you sent?

this is how my file looks like when created. all the places there are formulas are displayed #valuetavakoli.jpg

did the attachment work now?
from row 44 and down the formulas are index formulas with some other things in it getting data from other sheets. is that maybe why it wont work, that the sheet is getting information from other sheets? what do think might be the problem?

Originally Posted by tavakoli10

Attachment 663743

this is how my file looks like when created. all the places there are formulas are displayed #value

You have to reattach your file once more. Use Go Advanced / Manage Attachments option to upload file.

p.s. Pal, please, please, do not comment your own posts. use EDIT option.

EDIT: I've testes macro on sheet where are lots of weird formulas, everything goes smoothly, all has been created with values. So I think there is some error on your side. in your sheet)