Copy excel formulas without referencing previous worksheet in vb.net

Question

I currently have it copying an entire worksheet from File A. to file B. some of the worksheets just have to copy the values and no formulas. There is 1 particular worksheet that i need to copy the format along with the formulas but i don't want the formulas to reference the original file. i want it to reference the sheets in the new file.. How can i copy the worksheet/formulas and not referencing the original file.

Below i have code that copies a worksheet and then replaces the formulas with just values for the one off worksheets.

  xlWorksheetSource = xlWorkbookSource.Sheets(sheet.Name)
                 xlWorksheetSource.Copy(After:=xlWorkbookDestination.Worksheets(xlWorkbookDestination.Worksheets.Count))

                xlWorkDestSource = xlWorkbookDestination.Worksheets(xlWorkbookDestination.Worksheets.Count)
                ws = xlWorkDestSource

With ws.UsedRange
                   .Copy()
                    .PasteSpecial(Paste:=XlPasteType.xlPasteValues, _
                    Operation:=XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False)
                    xlApp.CutCopyMode = False
 End With

Error when trying your solution pasteformulas

  .Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Target, Me.Range("BE4")) Is Nothing Then
BE4 = Target.Value

If BE4 = "X" Then
    Worksheets("Invoice 2").Visible = xlSheetVisible
    Exit Sub
End If

If BE4 = "" Then
    Worksheets("Invoice 2").Visible = xlSheetVeryHidden
    Exit Sub
End If

End If

Answer 1

Change the first parameter value of PasteSpecial to xlPasteFormulas

.PasteSpecial(Paste:=XlPasteType.xlPasteFormulas, _     
Operation:=XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False)
                xlApp.CutCopyMode = False

Answer 2

Here is one approach in VBA, hope you can easily convert in .Net, I don't have VisualStudio on this machine so can't write VB.Net. Plus I am not so good with VB.Net

You first load all the formulas in an array (it loads values too, in case you have mixed cells), then in the array just update Sheet name (or wb name or anything that you want) and then dump the array to the destination. Then copy again and pastespecial only formats.

Sub test()

    Dim arr

    Dim strSheetFrom        As String
    Dim strSheetTo          As String

    Dim lctrRow             As Long
    Dim lctrCol             As Long

    strSheetFrom = "Sheet3"
    strSheetTo = "Sheet2"


    '/ First: Update your formulas in array and simply dump them.
    arr = Sheet2.UsedRange.Formula

    For lctrRow = LBound(arr, 1) To UBound(arr, 1)
        For lctrCol = LBound(arr, 2) To UBound(arr, 2)
            arr(lctrRow, lctrCol) = Replace(arr(lctrRow, lctrCol), strSheetFrom, strSheetTo)
        Next
    Next

    Sheet1.Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)) = arr

    '/ Now formats
    Sheet2.UsedRange.Copy
    Sheet1.Cells(1, 1).PasteSpecial xlPasteFormats
    Application.CutCopyMode = False

End Sub