Save Active Worksheet - Sever All Links

[Anne Troy]

Hi!
I want to save the active worksheet (yes, in my case it'll only be one sheet, and its name is "estimate") to a new workbook. I want to have the save as dialog auto-fill with the contents of I9, a hyphen, and then the date in I12 in ddmmyyyy format.

I need to sever all links and formulas, and save the values only.


Do I need to upload a sample file?

[Jacob Hilderbrand]

Try this.

Option Explicit

Sub Macro1()
Dim SaveAsName      As String
SaveAsName = Range("I9").Text & " - " & Format(Range("I12").Value, "ddmmyyyy")
Sheets("estimate").Copy
    With ActiveWorkbook.Sheets(1).UsedRange
        .Value = .Value
    End With
    Application.Dialogs(xlDialogSaveAs).Show SaveAsName
End Sub

[Anne Troy]

Yep. Exactly like that, except my mistake, which I fixed. I wanted mmddyyyy. LOL!!

Jake: You're the best. Do I say it often enough?

By the way. If you're feeling lazy, you might want to ask that it be added to the KB. If you're feeling energetic, you might want to add it yourself.

[Jacob Hilderbrand]

Glad to help

Take Care

[Anne Troy]

I knew it couldn't be so easy. Spoke to soon, Jake. Maybe it's not as hard as I think it'll be. How can I specify a path like MyDocuments\Estimates? (it currently goes to 1033, a system folder).

I have some data validation dropdowns. I want to clear those, too, while keeping the values. The total range I care about is A1:G32, if it matters. And there's dropdowns in:

A8:A21 and C4.

I also have some conditional formatting in C4 and E4 that I'd wanna dump.

Please?

[Jacob Hilderbrand]

How about this.

Option Explicit
 
Sub Macro1()
Dim SaveAsName      As String
'Specify default Save As name
    SaveAsName = Range("I9").Text & " - " & Format(Range("I12").Value, "ddmmyyyy")
'Specify default Save As path
    ChDir "C:\"
'Copy sheet to a new workbook
    Sheets("estimate").Copy
'Replace formulas with values
    With ActiveWorkbook.Sheets(1).UsedRange
        .Value = .Value
    End With
'Delete Validation and Conditional Formatting
    With ActiveWorkbook.Sheets(1).Cells
        .Validation.Delete
        .FormatConditions.Delete
    End With
'Show the Save As Dialog
    Application.Dialogs(xlDialogSaveAs).Show SaveAsName
End Sub

[Anne Troy]

Lookin good!!!!

[Jacob Hilderbrand]

If you will not know the path to the "MyDocuments" folder you can use this.

ChDir CreateObject("WScript.Shell").SpecialFolders("MyDocuments")

[Anne Troy]

Actually, I've asked what specific folder, and haven't got a reply yet. This is fine. Thanks.

[Anne Troy]

Okay. One wast wittle pwobwem.

The worksheet is protected.

I was able to tweak the code to unprotect it (believe it??), but it's doing it on the original worksheet instead of the new worksheet.

Ooops! I did it!!

Sub NewEstimate()
Dim SaveAsName      As String
'Specify default Save As name
    SaveAsName = Range("I9").Text & " - " & Format(Range("I12").Value, "mmddyyyy")
'Specify default Save As path
    ChDir "C:\"
'Copy sheet to a new workbook
Sheets("estimate").Copy
'Replace formulas with values
    ActiveWorkbook.Sheets(1).Unprotect
With ActiveWorkbook.Sheets(1).UsedRange
        .Value = .Value
    End With
'Delete Validation and Conditional Formatting
    With ActiveWorkbook.Sheets(1).Cells
        .Validation.Delete
        .FormatConditions.Delete
    End With
'Show the Save As Dialog
    Application.Dialogs(xlDialogSaveAs).Show SaveAsName
End Sub

Jake: You just might teach me this crap yet.

[Jacob Hilderbrand]

I'm trying.

Just a minor change so the comments make sense.

Sub NewEstimate() 
Dim SaveAsName      As String 
'Specify default Save As name
    SaveAsName = Range("I9").Text & " - " & Format(Range("I12").Value, "mmddyyyy") 
'Specify default Save As path
    ChDir "C:\" 
'Copy sheet to a new workbook
    Sheets("estimate").Copy 
'Remove sheet protection
    ActiveWorkbook.Sheets(1).Unprotect Password:=""
'Replace formulas with values
    With ActiveWorkbook.Sheets(1).UsedRange 
        .Value = .Value 
    End With 
'Delete Validation and Conditional Formatting
    With ActiveWorkbook.Sheets(1).Cells 
        .Validation.Delete 
        .FormatConditions.Delete 
    End With 
'Show the Save As Dialog
    Application.Dialogs(xlDialogSaveAs).Show SaveAsName 
End Sub

[Brandtrock]

Jake: You just might teach me this crap yet.

You gotta be careful Anne, this stuff is intoxicating. The more familiar you get with it, the more you want to know.

Regards,

[Anne Troy]

Oh! I don't know if I'd go THAT far... ROFL!!