My Macro turned off my formulas...how do I fix this? I have included the coding!

[excelblackbelt]

This macro takes a workbook and divides the sheets and saves them by name. Now the original workbook the values and formulas work, but when I run the macro and go into an individual sheet the formulas do not work...can someone help here?

Sub Copy_Every_Sheet_To_New_Workbook()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
Set Sourcewb = ThisWorkbook
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
MkDir FolderName
For Each sh In Sourcewb.Worksheets
If sh.Visible = -1 Then
sh.Copy
Set Destwb = ActiveWorkbook
With Destwb
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else
If Sourcewb.Name = .Name Then
MsgBox "Your answer is NO in the security dialog"
GoTo GoToNextSheet
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With
If Destwb.Sheets(1).ProtectContents = False Then
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
End If
With Destwb
.SaveAs FolderName _
& "\" & Destwb.Sheets(1).Name & FileExtStr, _
FileFormat:=FileFormatNum
.Close False
End With
End If
GoToNextSheet:
Next sh
MsgBox "You can find the files in " & FolderName
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Thank you,
Excel Black Belt

 

[SkipVought]

hi,

You change the calculation mode to MANUAL when you start the procedure and then change the calculation mode to AUTOMATIC at the end of your procedure.

If any formula needs to be calculated during the running of the procedure, you'll need to force the calculation of that formula or the sheet that formula is on or the entire application, 3 different levels of calculating.

If you want to force a calculate after you change the calculation mode to AUTOMATIC, then do an Application.Calculate.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

Thanks, Skip, for the response.

Let me give your more detail that way you can tell me what I should do. Basically this is a Sales work sheet where a manager will enter their goals. I have formulas in how those goals are translated into bonus money. The problem is we enter the goals and see the formula, but the it appears everything is turned off.

For example:
Row 35 Column D (after the macro has split and saved the worksheets is blank)
Manager enters in that cell $500,000 (then a calculation should happen)

The problem is on another cell to do the calculation of the $500K the formula is there but doesn't perform the calculation.

What you have in response is how the Worksheet is setup already? What should I do? I'd love to call you on this. Please reply to this message, or you can send me your phone number in an email if you would like. Let me know.

Thank you,
Joey

 

[SkipVought]

Okay, view this FAQ faq707-4594

and at the bottom there's a link to Send a Comment to SkipVought About This FAQ

Do that, and I'll reply via eMail.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

Be careful with setting calculation to manual. This setting propagates like a virus. Workbook stores this setting while saving. Workbook opened with manual calculation saved changes calculation to manual for all opened workbooks, this setting "infects" other worbooks saved later. So you should have a really good reason to use it, if it is possible avoid saving workbook in this state of application.

combo

 

[PHV]

Isn't this the culprit ?
.Cells.PasteSpecial xlPasteValues

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

The OP stated that his formulas were turned off, not that values replaced his formulas.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[excelblackbelt]

In order to keep my formulas live after the macro splits and saves what do I need to do? I see the formulas but when I drop values into fields calculations are not working...PLEASE HELP using my code?

 

[SkipVought]

Please post the formula for any formula that is not working so that we can talk to something specific.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

In order to keep my formulas live after the macro splits and saves what do I need to do?

As in my post above: either do not save workbook with calculation set to manual or manually (or with macro) set calculation to automatic if you opened workbook with manual calculation saved.

combo

 

[excelblackbelt]

Combo:

Before you posted this I took your direction and changed both to "automatic" and that did the trick. Thanks for the tip - it totally worked.

Thank you,
excelblackbelt

 

[SkipVought]

@ebb
Hey, I made that suggestion yesterday and you seemed to ignore it as you came back with some other circumstance???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}