VBA - Save Copy of Worksheet to Specific File Path AFTER Data Refresh

[lager1001]

To the VBA experts:

I need VBA code that will take the active worksheet (Sheet1), the only worksheet in the workbook, and copy and paste the values into a new workbook and save to a specified directory/folder, after I do a Data-Refresh All. It's essentially archiving a copy each time the data is refreshed.

I have the following code but I need it coded properly to be triggered upon the event of the refresh. Any ideas?

Sub SaveCopy()
Dim wsCopy As Worksheet, wsPaste As Worksheet
Dim wb As Workbook
Dim sFileName As String, sPath As String






'Path to store new file
sPath = "FilePath/Filepath/Filepath"
'Change filename as required
sFileName = "Archived_Report " & Format(Now(), "DD-MMM-YYYY hh mm AMPM") & ".xlsx"



'set the sheet you are copying. Change where neccessary
Set wsCopy = ThisWorkbook.Worksheets("Sheet1")
Set wb = Workbooks.Add
Set wsPaste = wb.Sheets(1)

'Copy everything from copy sheet
wsCopy.Cells.Copy
'Paste Values only
wsPaste.Cells.PasteSpecial xlPasteFormats
wsPaste.Cells.PasteSpecial xlPasteValues
Application.CutCopyMode = False


'Save new workbook

wsPaste.Name = "Sheet1" 'Change if needed
wb.SaveAs FileName:=sPath & sFileName, FileFormat:=xlOpenXMLWorkbook
MsgBox ("Report Archived")
wb.Close


End Sub

 

[DanteAmor]

after I do a Data-Refresh All.

What do you mean by "after I do a Data-Refresh All".
Is it an excel command that you execute, is it when you update a specific cell?