I using this code to copy a snapshot of sheet(1), renaming considering today's date and pasting as the last sheet in the workbook. However not sure how to break the pivot table link with datasource, making it not refreshable. Any suggestion,please?
Sub CopySheetRename() Dim S_ As Date Application.ScreenUpdating = False If RangeExists("Snapshot " & Format(Date, "dd-mmm-yyyy")) Then MsgBox "Sheet already exists." Else Sheets(1).Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = "Snapshot " & Format(Date, "dd-mmm-yyyy") End If Application.ScreenUpdating = True End Sub Function RangeExists(WhatSheet As String, Optional ByVal WhatRange As String = "A1") As Boolean Dim test As Range On Error Resume Next Set test = ActiveWorkbook.Sheets(WhatSheet).Range(WhatRange) RangeExists = Err.Number = 0 On Error GoTo 0 End Function