Macro: flatten sheet, delete other tab, and save

[steallan]

Hello - wondering if any vba experts can help me with what is a simple macro, but yet still well beyond my ability to write.

I have a workbook with a form where the user enteres a ID number, and then the rest of the form auto fills thanks to the formulas.

Now they need to be able to email it, and the backing data tab is enormous.

Can anyone write me a macro that:
1. Copies and pastes the whole of the Form tab as values, so converting all the formulas to their values.
2. Deletes the Data tab.
3. Saves the file as Save As.

So it'd open up the Save as box and stop there, so the user can then save it whereever they like. I'll then save the macro to a button on the Form tab.

Any help would be massively appreciated, my pathetic attempts to record such a macro......i shouldnt even admit i tried recording a macro. Its just sad.

Thank you

Ste

 

[Georgiboy]

Perhaps the below will get you started:

Sub test()
    Sheets("Form").Copy
    ActiveSheet.UsedRange.Copy
    ActiveSheet.UsedRange.PasteSpecial xlValues
    Application.Dialogs(xlDialogSaveAs).Show
End Sub

 

[kevin9999]

Another option

Sub steallan()
    With Worksheets("Form").UsedRange
        .Value = .Value
    End With
    Application.DisplayAlerts = False
    Worksheets("Data").Delete
    Application.DisplayAlerts = True
    Application.GetSaveAsFilename
End Sub

 

[steallan]

Perhaps the below will get you started:

Sub test()
    Sheets("Form").Copy
    ActiveSheet.UsedRange.Copy
    ActiveSheet.UsedRange.PasteSpecial xlValues
    Application.Dialogs(xlDialogSaveAs).Show
End Sub

Worked a treat, thank you sir

 

[steallan]

Another option

Sub steallan()
    With Worksheets("Form").UsedRange
        .Value = .Value
    End With
    Application.DisplayAlerts = False
    Worksheets("Data").Delete
    Application.DisplayAlerts = True
    Application.GetSaveAsFilename
End Sub

Thank you, much appreciated