VBA To Copy a sheet into another workbook with formulas without reference

[drop05]

Hello, i am wondering if there is a way that when copying a sheet into a new workbook, and the sheet i am copying has formulas. Is there a way to do this and not have the references in the formula of the new workbook when bringing the copy over?

Here is the code i use to make the tab in the new workbook.

    'Create new template
    Set wbNew = Workbooks.Add
    Set wsNewTemp1 = wbNew.Worksheets("Sheet1")
    wsNewTemp1.Name = "Calculation"
    ThisWorkbook.Sheets("Calculation").Range("A1:EW1").EntireColumn.Copy
    wsNewTemp1.Range("A1").PasteSpecial Paste:=xlPasteAll

 

[Herakles]

Use xlPasteValues instead of xlPasteAll

'Create new template
    
Set wbNew = Workbooks.Add
    Set wsNewTemp1 = wbNew.Worksheets("Sheet1")
    wsNewTemp1.Name = "Calculation"
    ThisWorkbook.Sheets("Calculation").Range("A1:EW1").EntireColumn.Copy
    wsNewTemp1.Range("A1").PasteSpecial Paste:=xlPasteValues
    Range("A1").Select
    Application.CutCopyMode = False

 

[drop05]

Use xlPasteValues instead of xlPasteAll

'Create new template
   
Set wbNew = Workbooks.Add
    Set wsNewTemp1 = wbNew.Worksheets("Sheet1")
    wsNewTemp1.Name = "Calculation"
    ThisWorkbook.Sheets("Calculation").Range("A1:EW1").EntireColumn.Copy
    wsNewTemp1.Range("A1").PasteSpecial Paste:=xlPasteValues
    Range("A1").Select
    Application.CutCopyMode = False

Thank you, is there a way to use it for the vba code below, when using the below code it is bringing over the external references in the formulas, i would like to keep the formulas when bring them over but without the external references and not just values

        Workbooks.Add
        ThisWorkbook.Worksheets(shtUnit.Name).Copy before:=ActiveWorkbook.Worksheets("Sheet1")

 

[Herakles]

Can you let us know what the formula's are and where they are?

Are you able to use Xl2BB.

 

[mohadin]

Hi drop05
Try this method I think it do the trick
Run this code when the sheet you want to copy

Sub test()
 Cells.SpecialCells(xlCellTypeFormulas, 23).Replace What:="=", Replacement:=Chr(135) & "="
 Sheets("Sheet1").Copy
 Cells.Replace What:=Chr(135) & "=", Replacement:="="
ThisWorkbook.Sheets("Sheet1").Cells.Replace What:=Chr(135) & "=", Replacement:="="
End Sub

 

[drop05]

Hi drop05
Try this method I think it do the trick
Run this code when the sheet you want to copy

Sub test()
 Cells.SpecialCells(xlCellTypeFormulas, 23).Replace What:="=", Replacement:=Chr(135) & "="
 Sheets("Sheet1").Copy
 Cells.Replace What:=Chr(135) & "=", Replacement:="="
ThisWorkbook.Sheets("Sheet1").Cells.Replace What:=Chr(135) & "=", Replacement:="="
End Sub

yes this helps bring the formula over, however the formula is staying as ‡= after the vba is ran, is there a way to turn it back into a formula

 

[mohadin]

The code above does every thing in here

Sub test()
 Cells.SpecialCells(xlCellTypeFormulas, 23).Replace What:="=", Replacement:=Chr(135) & "=" '<< This line to replace in the source sheet
 Sheets("Sheet1").Copy ' Sopy to a new book
 Cells.Replace What:=Chr(135) & "=", Replacement:="=" ' Replace in the new book so you get back to formulas
ThisWorkbook.Sheets("Sheet1").Cells.Replace What:=Chr(135) & "=", Replacement:="=" 'Replace in the source book so you get back to formulas
End Sub

 

[drop05]

The code above does every thing in here

Sub test()
 Cells.SpecialCells(xlCellTypeFormulas, 23).Replace What:="=", Replacement:=Chr(135) & "=" '<< This line to replace in the source sheet
 Sheets("Sheet1").Copy ' Sopy to a new book
 Cells.Replace What:=Chr(135) & "=", Replacement:="=" ' Replace in the new book so you get back to formulas
ThisWorkbook.Sheets("Sheet1").Cells.Replace What:=Chr(135) & "=", Replacement:="=" 'Replace in the source book so you get back to formulas
End Sub

Hi sorry for the late ask, but have been trying to trouble shoot this but around here

Cells.Replace What:=Chr(135) & "=", Replacement:="=" ' Replace in the new book so you get back to formulas

the code is not replacing the new workbooks formulas it is leaving them as below

‡=SUBTOTAL(109,tbl_ExpbyFiling[TI (Line 30)])

and not getting rid of the ‡

this is in regards to the external workbook it is creating, the workbook the mod is in works good

 

[mohadin]

Hi
Then try this

Sub test()
 Cells.SpecialCells(xlCellTypeFormulas, 23).Replace What:="=", Replacement:=Chr(135) & "=" '<< This line to replace in the source sheet
 Sheets("Sheet1").Copy ' Sopy to a new book
 ActiveWorkbook.Sheets("sheet1").Cells.Replace What:=Chr(135) & "=", Replacement:="=" ' Replace in the new book so you get back to formulas
ThisWorkbook.Sheets("Sheet1").Cells.Replace What:=Chr(135) & "=", Replacement:="=" 'Replace in the source book so you get back to formulas
End Sub

 

[NdNoviceHlp]

Hi drop05 and mohadin. Trial passing the sheet in a collection. HTH. Dave

Dim WbNew As Workbook, ShtCollect As Collection
Set ShtCollect = New Collection
ShtCollect.Add ThisWorkbook.Sheets("Sheet1")
Set WbNew = Workbooks.Add
ShtCollect(1).Copy before:=WbNew.Sheets("Sheet1")