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