Beatrix
10-16-2012, 09:14 AM
Hi Everyone ,
I have below script which produces templates. Formula Calculation option is Automatic in main workbook however it goes manual in templates. I need to make formula calculation Automatic as default. Would you help me to edit the script to be able to do this please?
Cheers
Yeliz
Sub make_files()
Dim wb As Workbook, wbCode As Workbook, wsUsers As Worksheet
Dim LR As Long
Dim User_ID As String, User_Name As String
Const fPath As String = "C:\Excel VBA\master\" 'change to your path
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Calc = .Calculation
.Calculation = xlCalculationAutomatic
End With
Set wbCode = ThisWorkbook
Set wsUsers = wbCode.Worksheets("users")
With wsUsers
LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = 2 To LR
User_ID = .Range("A" & i).Value
User_Name = .Range("B" & i).Value
Set wb = Workbooks.Open(fPath & "master.xls")
wb.Worksheets("H-POD").Range("M8") = User_Name
wb.SaveAs (fPath & "H-POD_" & User_ID & "_v02"), FileFormat:=17
wb.Close False
Next
End With
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = Calc
End With
End Sub
I have below script which produces templates. Formula Calculation option is Automatic in main workbook however it goes manual in templates. I need to make formula calculation Automatic as default. Would you help me to edit the script to be able to do this please?
Cheers
Yeliz
Sub make_files()
Dim wb As Workbook, wbCode As Workbook, wsUsers As Worksheet
Dim LR As Long
Dim User_ID As String, User_Name As String
Const fPath As String = "C:\Excel VBA\master\" 'change to your path
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Calc = .Calculation
.Calculation = xlCalculationAutomatic
End With
Set wbCode = ThisWorkbook
Set wsUsers = wbCode.Worksheets("users")
With wsUsers
LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = 2 To LR
User_ID = .Range("A" & i).Value
User_Name = .Range("B" & i).Value
Set wb = Workbooks.Open(fPath & "master.xls")
wb.Worksheets("H-POD").Range("M8") = User_Name
wb.SaveAs (fPath & "H-POD_" & User_ID & "_v02"), FileFormat:=17
wb.Close False
Next
End With
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = Calc
End With
End Sub