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 :beerchug:

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!!!!
:cloud9:

[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. :thumb

Regards,

[Anne Troy]

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