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

lager1001

New Member
Joined
May 17, 2019
Messages
41
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,258
Office Version
2007
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,090,363
Messages
5,414,011
Members
403,511
Latest member
Emmanuel John

This Week's Hot Topics

Top