Automatic Calculation

[melewie]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am having problems with returning a workbook to automatic calculation, I am using the below code<o></o>
<o></o>

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Application.Calculation = xlManual<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Lots of code<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Application.Calculation = xlCalculationAutomatic<o:p></o:p>[/FONT][/COLOR]

<o></o>
<o></o>
I have stepped through the code and it is definitely reading the code but not changing the WB back to auto calculation. I am fully stuck with this and any help would be greatly appreciated.<o></o>

 

[diddi]

what has happened is that you have applied app.calc to the wrong workbook.

 

[melewie]

what has happened is that you have applied app.calc to the wrong workbook.

I have activated the WB before app.calc = man and before app.calc=auto. Do I need to do something else?

 

[Richard Schollar]

Can we see your whole code?

Application.Calculation, like its name suggests, is an Application level setting so it shouldn't matter which workbook was active at the time you set it.

 

[melewie]

Whole Code Below

Sub Refresh_Major_Data()
Dim wBook As Workbook
Dim wBookExec As Workbook
Dim RowNo As Double 'define row number
Dim ColNo As Integer 'define number of columns to look at
Application.ScreenUpdating = False ' Stop screen updates
Application.Calculation = xlManual 'manual calculation to stop errors
Set wBook = Workbooks("Major_Projects_Master_List")
Set wBookExec = Workbooks("Executive Project Dashboard")
'*******************'
'Clear previous data'
'*******************'
Sheets("Major Data").Select
Cells.ClearContents
'*********************'
'Get Data From Tracker'
'*********************'
wBook.Activate
If Sheets("£1m+ Active Projects").Visible = False Then
    Sheets("£1m+ Active Projects").Visible = True
    Sheets("£1m+ Active Projects").Select
Else
    Sheets("£1m+ Active Projects").Select
End If
Cells.Copy
wBookExec.Activate
Sheets("Major Data").Select
Range("A1").PasteSpecial Paste:=xlPasteValues
Rows("1:8").Select
Selection.Delete Shift:=xlUp
'*****************'
'Delete Blank rows'
'*****************'
NumRows = Cells(65000, 1).End(xlUp).Row
ColNo = 1
For RowNo = NumRows To 1 Step -1
        
        If Cells(RowNo, ColNo).Value = "" Then
            Cells(RowNo, ColNo).EntireRow.Delete Shift:=xlUp
        
        End If
    Next RowNo
'**************************'
'Change UK - United Kingdom'
'**************************'
NumRows = Cells(65000, 1).End(xlUp).Row
ColNo = 4
For RowNo = NumRows To 1 Step -1
        
        If Cells(RowNo, ColNo).Value = "UK" Then
            Cells(RowNo, ColNo).Value = "United Kingdom"
        
        End If
    Next RowNo
'************************'
'Get  Major Pipeline Data'
'************************'
Sheets("Major Pipeline Data").Select
Cells.ClearContents
wBook.Activate
If Sheets("Assessment Projects").Visible = False Then
    Sheets("Assessment Projects").Visible = True
    Sheets("Assessment Projects").Select
Else
    Sheets("Assessment Projects").Select
End If
Cells.Copy
wBookExec.Activate
Sheets("Major Pipeline Data").Select
Range("A1").PasteSpecial Paste:=xlPasteValues
Rows("1:5").Select
Selection.Delete Shift:=xlUp
'**************************'
'Change UK - United Kingdom'
'**************************'
NumRows = Cells(65000, 1).End(xlUp).Row
ColNo = 4
For RowNo = NumRows To 1 Step -1
        
        If Cells(RowNo, ColNo).Value = "UK" Then
            Cells(RowNo, ColNo).Value = "United Kingdom"
        
        End If
    Next RowNo
'*************************************'
'End Sub Home Page - Application reset'
'*************************************'
Sheets("Combined Dashboard").Select
Range("A1").Select

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

Thanks for your help

 

[melewie]

Not sure what I have done, but I have just run it again and it now works?!?! Sometimes VB confuses the hell out of me