Hi guys,
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?
Code
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