Good Day All!
I am new to the forum on a recommend from a colleague. I have not found anything that fits my particular issue. I have used the code below to create the values only copy of the workbook. The issue is the copy does not keep the formatting nor the filters in tact. I'm not sure if this is possible but any suggestions would be appreciated.
Thanks for your time.
Sub CreateValuesOnly() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim Output As Workbook, Source As Workbook Dim sh As Worksheet Dim FileName As String FileName = "C:\Users\blittlej\Desktop\ValuesOnly_.xlsx" Dim firstCell Dim curdate As String curdate = Format(Now(), "yyyy-MM-dd") Set Source = ActiveWorkbook Set Output = Workbooks.Add Output.SaveAs FileName Dim i As Integer For Each sh In Source.Worksheets Dim newSheet As Worksheet ' select all used cells in the source sheet: sh.Activate sh.UsedRange.Select Application.CutCopyMode = False Selection.Copy ' create new destination sheet: Set newSheet = Output.Worksheets.Add(after:=Output.Worksheets(Output.Worksheets.Count)) newSheet.Name = sh.Name ' make sure the destination sheet is selected with the right cell: newSheet.Activate firstCell = sh.UsedRange.Cells(1, 1).Address newSheet.Range(firstCell).Select ' paste the values: Range(firstCell).PasteSpecial Paste:=xlPasteValues Range(firstCell).PasteSpecial Paste:=xlPasteFormats 'Range(firstCell).PasteSpecial Paste:=xlPasteColumnWidths, _ 'Operation:=xlNone, SkipBlanks:=True, Transpose:=False Next ' delete the sheets that were originally there While Output.Sheets.Count > Source.Worksheets.Count Output.Sheets(1).Delete Wend FileName = "C:\Users\blittlej\Desktop\ValuesOnly_" & curdate & ".xlsx" Output.SaveAs FileName Output.Close Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub