Search code examples
excelvba

Copy sheet to another workbook without reference to original wookbook

I have a sheet with formula reference from beside sheet.

=SUMIF(NAIJIW!$X$4:$X$27460, $C8, NAIJIW!I$4:I$27460)

When i copy this sheet to another workbook, that formula change by reference to original workbook

Reference to original workbook

How to copy that sheet to another workbook without refence to original workbook? don't reference to orignal workbook

Solution

  • Another way to achieve this with VBA code. But keep in mind that the destination workbook must contain a worksheet named NAIJIW.

    Sub CopySheetNoLinks()
    ThisWorkbook.Sheets("Sheet2").Copy After:=Workbooks("DestBook.xlsx").Sheets(1)
    ActiveWorkbook.ChangeLink Name:=ThisWorkbook.Name, _
        NewName:=ActiveWorkbook.Name, Type:=xlExcelLinks
End Sub

    Sheet2 is the name of sheet to be copied, DestBook.xlsx is the destination workbook name. The destination workbook must be open. The Sheet2 will be copied after the first sheet in the destination. You can change this if necessary.