Copy sheets into newly created workbook AND remove formulas

Question

I have a workbook for reporting. Every entry is a vlookup. I copy 4 sheets from this workbook into a new workbook as an "outfile" that keeps the numbers but removes the formula. I have a successful macro that copies the 4 sheets into a newly created workbook, but it keeps the sheets as is and does not remove formulas. I am trying to add code that removes the formulas, While there are several online sources for removing formulas, I am not able to adapt them to my macro.

Heres what I have so far: (the first 2 lines work, but adding the 3rd does not)

Sub outfile()  
ThisWorkbook.Sheets(Array("Sheet 4","Sheet 5","Sheet 6","Sheet 7")).Copy  
ActiveWorkbook.SaveAs "C:\example\outfile", FileFormat:=51  
Workbook("outfile").Sheets(Array("Sheet 4","Sheet 5","Sheet 6","Sheet 7")).UsedRange.Value = Workbook("outfile").Sheets(Array("Sheet 4","Sheet 5","Sheet 6","Sheet 7")).UsedRange.Value
END SUB

SJR SJR · Accepted Answer · 2017-10-06T12:27:55

There might be a cleverer way, but you could loop

Sub outfile()

Dim ws As Worksheet

ThisWorkbook.Sheets(Array("Sheet 4", "Sheet 5", "Sheet 6", "Sheet 7")).Copy

For Each ws In ActiveWorkbook.worksheets
    ws.UsedRange.Value = ws.UsedRange.Value
Next ws

ActiveWorkbook.SaveAs "C:\example\outfile", FileFormat:=51

End Sub

Copy sheets into newly created workbook AND remove formulas

1 Answers