Copy Sheet to Same Workbook but Values, Formatting Only with a Twist....

[arossijr]

Good Afternoon All:

I am trying to Copy a Sheet to the Same workbook Rename it and have it as Values only so that it can be copied by itself into a new workbook.
Or better yet, just Copy the Sheet into New Workbook as Values only... But there is a catch... I am using Indirect in the formulas on the sheet and is hindering my normal copy method.

For my sheets without the Indirect formulas i have:

Sheet.Copy After:=Newbook.sheets(Newbook.sheets.count)
Newbook.sheets(sheet.name).UsedRange.Cells.Value = Newbook.Sheets(Sheet.Name).UsedRange.Cells.Value

When I do this the Formulas with Indirect Break and well book its broken...
I was trying to use ActiveSheet.PasteSpecial xlPasteValues but I am getting an error..Then Excel for some reason just blows up...

 

[arossijr]

I was able to piece together some code that is working. But for some reason the Sheet that is copied out and saved is being corrupted. When the sheet is opened i get the error about it being corrupted, i choose fix and it opens and tells me that the tables bin was corrupted.
How can i prevent this?