Macro to copy values to new sheet and prompt user where they want to save
- by MasterJedi
I'm looking to create a macro in Excel 2007 which will do the following:
copy the formatting and values from a sheet called 'report',
insert this into a new workbook, naming the file with the value in 'B9' and appending the word 'report' to this
when copying the data into a new workbook, the formatting of the original should be retained, but cell contents pasted as values rather than formulas
prior to saving the file with the above name, the user will be prompted to choose their folder
at the end, a message will be displayed alerting the user that the file has been saved successfully
Any assistance would be much appreciated. Thanks.
I have attempted to do this, but my experience with VBA is limited. Here is what I have done so far...
Application.DisplayAlerts = False
Worksheets("Report").Copy
With ActiveSheet.UsedRange
.Value = .Value
End With
ActiveSheet.DrawingObjects.Delete
Set wbNew = ActiveWorkbook
wbNew.SaveAs Application.GetSaveAsFilename
wbNew.Close True
Application.DisplayAlerts = True
