Rob_010101
Board Regular
- Joined
- Jul 24, 2017
- Messages
- 198
- Office Version
- 365
- Platform
- Windows
Hello,
My excel is a master document that pulls in data from different excel sheets in different departments, each of which are all updated daily. I need to save the "master" excel once a week into a different excel with all the values pasted. This creates a weekly snapshot of the data as it is at the point of saving but leaves the "master" excel with all the formulas, so it keeps updating.
I have tried to use the below assigned to a button however in addition to creating a new document with paste values, it is also pasting values in the "master" document rendering it useless.
It should "Save As" and save into a new excel, as the date, with values pasted. The "master" excel i.e. the sheet being saved from should retain all the formulas.
Hope that makes sense.
Chris
My excel is a master document that pulls in data from different excel sheets in different departments, each of which are all updated daily. I need to save the "master" excel once a week into a different excel with all the values pasted. This creates a weekly snapshot of the data as it is at the point of saving but leaves the "master" excel with all the formulas, so it keeps updating.
I have tried to use the below assigned to a button however in addition to creating a new document with paste values, it is also pasting values in the "master" document rendering it useless.
VBA Code:
Sub MakeBook()
Dim sh As Worksheet
For Each wb In Sheets
wb.Activate
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Next
With ActiveWorkbook
s = Replace(.FullName, .Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
End With
ActiveWorkbook.SaveAs Filename:=s
End Sub
It should "Save As" and save into a new excel, as the date, with values pasted. The "master" excel i.e. the sheet being saved from should retain all the formulas.
Hope that makes sense.
Chris