Copy of worksheet is missing the data

[PacVII]

Using 365 and trying to create a copy of a worksheet that has formulas within the cells. When i go to create a copy (to attach to an email) the worksheet looks as it should, then a second later the data with the formulas goes blank however the other fields that do not have formulas stays. The odd part, some of the fields that do have a formula will stay but most disappear. seems like it will keep some with a VLookUp but others with an index and match go away.

Any idea how to create a copy and keep the values showing? The odd part of this is i have another workbook and this works just fine. I can not figure out why its not working on this new workbook?

I am doing the same steps on each. Go to the tab with the sheet name, right click on the tab name and select Move or Copy, using 'to book' and 'new' . One sheet it works and the date stays the other it goes blank....

 

[PacVII]

I think I found a work around:

OldWB.ActiveSheet.Cells.Select
Selection.Copy

NewWB.Activate
NewWB.ActiveSheet.Cells.Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, SkipBlanks:=False, Transpose:=False