calculation VBA and merge 2 VBA code.

[Jagat Pavasia]

dear Sir/madam,

I have 3 sheet in my workbook, sheet1, sheet2, & Sheet3.
Mostly i worked for data entry in Sheet1 only. And sheet1 has large data till about 15000 rows and column till "R".
Sheet2 and Sheet3 has only formula to get data from Sheet1.

So, all over the data entry speed is now getting low and lag in Sheet1.
If i select calculation mode manual from Formulas menu in excel, then my speed is too much faster.

So, i want that sheet1 is automatic calculation is always ON mode.
Sheet2 & 3 calculation ON only when i tab to Sheet2 or Sheet3, and when i back to Sheet1 then stop calculation in Sheet2 &3.


Have any idea of VBA code, then please give me VBA code,

I have already another VBA code in my worksheet in Sheet1.
which i shows you below.

Private Sub Worksheet_Change(ByVal Target As Range)
'   Code goes in the Worksheet specific module
    Dim rng As Range
    '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("b:b,f:f,m:m")
        '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
        '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
        '   Action if Condition(s) are met (do your thing here...)
            If Target.Value = "**" Then Target.Value = Format(Date, "mm/dd/yyyy")
            
    End Sub

 

[CountTepes]

The information in the link might help.

Worksheet.Calculate method (Excel)

Office VBA reference topic

docs.microsoft.com

In your workksheet_change event, put in the commands to calculate the sheets you want.

Also if you put in

Application.ScreenUpdating = False

at the beginning of the code and

Application.ScreenUpdating = True

at the end

When you are working with WorkSheet_Change events, it you should turn off events before the code makes any changes to the sheet. Otherwise it will be called again, by the change it made.

So you should have at the beginning and end;

Application.EnableEvents = False

Application.EnableEvents = True

 

[Fluff]

Duplicate Calculation option VBA code

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).