CODEWITHSUNDEEP
excelvb.net
1986G1988
1986G1988

Reputation: 152

Copy sheets between two Workbooks that have Formulas in it

I am trying to copy sheets between to two workbooks. This is the code that i have. xlWorkbook contains the data and i want to add sheets from xlDesTemplatebook that contains all my formulas.The code works and copies all the sheets but cells with formulas are appearing as #REF & if i select the cell having formulas and then hit enter it works.But my problem is that i am using the xlworkbooklater in my program for which i need the calculated data not the #REF in those cells. How can i fix this ?

 xlWorkBook = xlApp.Workbooks.Open(TextBox1.Text)
 XlDesTemplateBook=xlApp.Workbooks.Open("C:\Users\user\Desktop\Formulas.xlsx")
 Dim oSheetsList() As Object = {"IB6", "IB7", "IOB6", "IOB7", "LI", "LO", "VB"}
 XlDesTemplateBook.Sheets(oSheetsList).Copy(After:=xlWorkBook.Worksheets("PLC EXPORT-VB"))

Screenshot Showing my issue

enter image description here

Upvotes: 0

Views: 31

Answers (1)

Solar Mike
Solar Mike

Reputation: 8375

I have got round this by doing the following:

1) make a copy of the sheet you want to send

2) select all or just the cells with formulae

3) do edit/ replace and replace = with xyxyxy or something that never appears

4) move the sheet over to the other workbook

5) select all and replace xyxyxy with =

Always works fine for me...

Upvotes: 1

Related Questions