Can the following Vba's be improved to run quicker.

Hi and thanks for looking at my post.

I have several Vba's in the enclosed Calendar worksheet, however have I compiled them correctly (as still learning Vba) as they seem to run a bit slow.

I do not take any credit for the original non Vba Calendar (an Excel Guru created it) I have added on to it.

Calender v1.xls

I would be most grateful if anyone has any tips or suggestions on how to speed up the Vba calculations

Many thanks

Toonies

on the vba's I use the following

Application.ScreenUpdating = False

code

Application.ScreenUpdating = True

I have merged a 3 Vba's together into the following

Sub Macro1()
'
' Macro1 Macro
'

'
Dim LR As Long
Application.ScreenUpdating = False
    With Sheets("Calendar Items").Select


LR = Range("A" & Rows.Count).End(xlUp).Row

With Range("A1:C" & LR)
    .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    .Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
End With
End With
Sheets(3).Unprotect userinterfaceonly = True
    
    Sheets("Summary").Select
    Range("C3:D3").Select
    Selection.Copy
    Range("C4:C50").Select
    ActiveSheet.Paste
    Range("A3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""

    Range("A3").Select
Sheets(3).Unprotect userinterfaceonly = True
With Sheets("Calendar Items").Select
    Range("A2:C3001").Find(What:="", After:=Range("A2:C3001").Cells(1), LookIn:=xlFormulas, LookAt _
 :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
 False).Select
End With
Call NamedRange1

Application.ScreenUpdating = True
End Sub

Because workbook contains many formulas, during execution macros you need turn off the calculation.
It should be also turn off a reaction to the events.

I rebuilt the whole somewhat.
See attachment.

Artik

hi now thats a nice piece of work

I do have one question

when I select a name with the dropdown menu on the summary page is there any way that the calculation could be smoother.

What I mean is that you can see the calculation occur.

It copy from the 1st row C3:D3 downwards and then shows the same in each row to C4:D50 whilst calculating

Is it possible not to see the calculation happen if you know what I mean

Originally Posted by Toonies

Is it possible not to see the calculation happen

In this solution, it is rather impossible, because when you select a name with the dropdown, in the first the sheet is calculation, and only then the macro is run.

But you can get rid of complex formulas. See attachment.

Artik

Your solution works a treat.

Can I ask where did you learn about VBA, have you got any good pointers or books that may improve my knowledge

Many thanks

Toonies

I grew up on Walkenbach's books. Solving the problems of other people, on forums like this, it is a great training too.

Artik

I have amend the calendar as normal with Excel not all Datepickers work with each version

I have implemented your amended VBA's however I now find that the date transfer from the userform calendar is running slow again everything else works ok.


popup calendar1.xls