Excel Vba Copy Current sheet in new workbook and make all Formula and VBA result as Value

[aqeelnokia99]

Hello Expert ,
i am looking @ VBA which Copy sheet into new workbook and make all formula and VBA as a value i found this on internet but its not make VBA result as Value
========
ON Sheet
========

Sub Seperate_Sheets()
Dim Path1 As String
Dim wb As Workbook
Dim part1 As String
part1 = Range("C2").Value


Path1 = "C:\" & part1 & " Costing.xlsx"

Sheets(Array("M C €")).Copy

Set wb = ActiveWorkbook

BreakAllLinks wb

wb.SaveAs Filename:=Path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


End Sub

==========
on madule
==========

Sub BreakAllLinks(ByVal wb As Object)
Dim Link As Variant, LinkType As Variant

For Each LinkType In Array(xlLinkTypeExcelLinks, xlOLELinks, xlPublishers, xlSubscribers)
If Not IsEmpty(wb.LinkSources(Type:=LinkType)) Then
For Each Link In wb.LinkSources(Type:=LinkType)
wb.BreakLink Name:=Link, Type:=LinkType
Next Link
End If
Next LinkType
wb.UpdateLinks = xlUpdateLinksNever
End Sub

 

[Georgiboy]

Maybe an adjustment as below:

Sub Seperate_Sheets()
Dim Path1 As String
Dim wb As Workbook, ws As Worksheet
Dim part1 As String
part1 = Range("C2").Value


Path1 = "C:\" & part1 & " Costing.xlsx"

Sheets(Array("M C €")).Copy

Set wb = ActiveWorkbook
Set ws = wb.Sheets(1)

ws.Cells.Copy
ws.Cells.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

BreakAllLinks wb

wb.SaveAs Filename:=Path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub

 

[aqeelnokia99]

its not working you know any macro which make copy of sheet into new workbook and make all formula and macro result as value

 

[Georgiboy]

Describe the 'not working' part?

Do you get a page full of errors?
Do you get a page full of active formulae?
Do you get a blank page?

Excel is a bit like a car at the garage - you don't take a car to the garage and say "It's not working" - you would usually give some detail about the fault to help the engineer.

 

[aqeelnokia99]

Hello Thanks for help when i run i got this error

 

[aqeelnokia99]

also this part become error after i make new sheet this get numeric is vba which get number from text

this is reason i need vba code that duplicate current sheet and make all VBA result as value to Avoid #Name Error

 

[Georgiboy]

Have you tried to copy the sheet inside the same file where the code is, copy all cells, paste as values, then output the sheet to another workbook.

Another option might be to copy your range, create a new workbook, paste values, paste formats, save the created workbook.

I think it is possibly trying to calculate after the sheet has been seperated from the code.

 

[aqeelnokia99]

i dont know VBA and i dont have any idea how to fix this script if you help me will great help i just want you make script that make cell C1 and D1 as value after make new copy