Copy excel formulas without referencing previous worksheet in vb.net

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

2 Answers

Answers 1

Change the first parameter value of PasteSpecial to xlPasteFormulas

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

Answers 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 

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati