Copy worksheets

[Darren Smith]

Hi all

How can i copy worksheets from a workbook then keep the formulas but delete links to previous workbook?

I tried this but no luck.

Please somebody help

Dim astrLinks As Variant

astrLinks = wkbDest.LinkSources(Type:=xlLinkTypeExcelLinks)


With wkbDest.Sheets(6)
   .Select
   .Unprotect
   .BreakLink Name:=astrLinks(6), _
    Type:=xlLinkTypeExcelLinks

   .Protect
End With

 

[footoo]

LinkSources is a workbook method, not a sheet method.
Try this instead (assumes the only external links are to the previous workbook) - not tested :

Dim cel As Range
For Each cel In wkbDest.Sheets(6).Cells.SpecialCells(xlCellTypeFormulas)
     If InStr(cel.Formula, "[") Then cel = cel.Value
Next

 

[Darren Smith]

The code takes away all formulas but they need to stay but links to previous worksheet broken

 

[footoo]

The code takes away all formulas but they need to stay but links to previous worksheet broken

It should not remove all formulas - only those with external links. Is that not what you want?

 

[Darren Smith]

Sorry I meant below,

The code takes away all formulas but they need to stay but links to previous workbook broken.
The formulas take their data from the same name worksheet on both workbooks.
If that makes sense

 

[footoo]

Try this :

wkbDest.Sheets(6).Cells.SpecialCells (xlCellTypeFormulas)
    .Replace What:="[*]", Replacement:="", LookAt:=xlPart

 

[Darren Smith]

Hi and Good Morning

I`ve tried this but the (. Replace) goes blue?? And says the Invalid or unqualified reference error.

Mt

 

[footoo]

wkbDest.Sheets(6).Cells.SpecialCells (xlCellTypeFormulas) _
    .Replace What:="[*]", Replacement:="", LookAt:=xlPart

 

[Darren Smith]

This is my code below for taking all worksheets to a new workbook. I`ve added your code to this but it still links to the previous workbook.
All I need is the formulas on Sheet 6 to transfer to a new workbook along with the rest of the sheets. The rest of the sheets are a straight copy & paste

Private Sub ListBox3_Click()

 
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    
    Set wkbDest = Workbooks("Automated Cardworker.xlsm")

          Do While wkbDest.Sheets.Count > 4
            wkbDest.Sheets(wkbDest.Sheets.Count).Delete
            Loop
        

    Set wkbSource = Workbooks.Open("\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker\Jobcard Templates\" _
                         & Body_And_Vehicle_Type_Form.ListBox3.List(Body_And_Vehicle_Type_Form.ListBox3.ListIndex))

        For Each sht In Workbooks(wkbSource.Name).Sheets
           sht.Copy After:=wkbDest.Sheets(wkbDest.Sheets.Count)
        Next sht
        
wkbSource.Close SaveChanges:=False

wkbDest.Sheets(6).Cells.SpecialCells(xlCellTypeFormulas) _
    .Replace What:="[*]", Replacement:="", LookAt:=xlPart

wkbSource.Close SaveChanges:=False

Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

 

[footoo]

Are you sure it's Sheets(6) ? Maybe it's Sheets("Sheet6"), or maybe Sheet6 ? Check the VB Editor for the sheet ref.
What sheet gets activated with :

wkbDest.Activate
Sheets(6).Activate