Shorten this VBA Code

H

Hans10

New Member
Joined
Jul 22, 2015
Messages
8
Hi
I have this Code, that i would like to shorten Because it's long and slow?.
Some of the line is recorded with the "record macro" function.
The pivottables pulls data from an OLAP Cube!
Can anyone help?

Sub RunAll()


'Kører følgende makro'er


Call Makro1


Call Makro2


Call Makro3


Call Makro4


Call Makro5


End Sub


Sub Makro1()


'Indsætter starttid for opdateringen


Application.Worksheets("T6").Range("A1") = Format(Now(), "HH:MM:SS")


End Sub


Sub Makro2()


' Ændrer dato'en i overskrifterne til dags dato minus 1 dag = Opdateringsdato


a = Format(Date - 1, "DD/MM/YYYY")


Application.Worksheets("T1").Range("A1") = "Overblik over forløb på områder, unikke borgere visiteret pr. " & a & " - " & "inkl. Plejeboliger"


Application.Worksheets("T1").Range("A23") = "Overblik over forløb på områder, unikke borgere visiteret pr. " & a & " - " & "ekskl. Plejeboliger"


End Sub


Sub Makro3()


'Opdaterer følgende pivottabeller fra følgende ark



Application.EnableEvents = False


Application.ScreenUpdating = False




Sheets("T1").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters


Sheets("T1").PivotTables("Pivottabel1").CubeFields(47).EnableMultiplePageItems = True


Sheets("T1").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")


Sheets("T1").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")


Sheets("T1").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")


Sheets("T1").PivotTables("Pivottabel1").PivotCache.Refresh



Sheets("T1").PivotTables("Pivottabel2").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters


Sheets("T1").PivotTables("Pivottabel2").CubeFields(47).EnableMultiplePageItems = True


Sheets("T1").PivotTables("Pivottabel2").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")


Sheets("T1").PivotTables("Pivottabel2").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")


Sheets("T1").PivotTables("Pivottabel2").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")


Sheets("T1").PivotTables("Pivottabel2").PivotCache.Refresh



Sheets("T2").PivotTables("Pivottabel1").PivotCache.Refresh



Sheets("T2").PivotTables("Pivottabel3").PivotCache.Refresh



Sheets("T3").PivotTables("Pivottabel1").PivotCache.Refresh



Sheets("T4").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters


Sheets("T4").PivotTables("Pivottabel1").CubeFields(47).EnableMultiplePageItems = True


Sheets("T4").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")


Sheets("T4").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")


Sheets("T4").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")


Sheets("T4").PivotTables("Pivottabel1").PivotCache.Refresh



Sheets("T5").PivotTables("Pivottabel1").PivotFields("[Dato].[Aar Maaned Dag].[Aar Maaned Dag]").ClearAllFilters


Sheets("T5").PivotTables("Pivottabel1").CubeFields(231).EnableMultiplePageItems = True


Sheets("T5").PivotTables("Pivottabel1").PivotFields("[Dato].[Aar Maaned Dag].[Aar Maaned Dag]").VisibleItemsList = Array("[Dato].[Aar Maaned Dag].&[20150725]")


Sheets("T5").PivotTables("Pivottabel1").PivotCache.Refresh



Sheets("T6").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters


Sheets("T6").PivotTables("Pivottabel1").CubeFields(47).EnableMultiplePageItems = True


Sheets("T6").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")


Sheets("T6").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")


Sheets("T6").PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")


Sheets("T6").PivotTables("Pivottabel1").PivotCache.Refresh



Sheets("T6").PivotTables("Pivottabel2").PivotCache.Refresh



Sheets("T6").PivotTables("Pivottabel3").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters


Sheets("T6").PivotTables("Pivottabel3").CubeFields(47).EnableMultiplePageItems = True


Sheets("T6").PivotTables("Pivottabel3").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")


Sheets("T6").PivotTables("Pivottabel3").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")


Sheets("T6").PivotTables("Pivottabel3").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")


Sheets("T6").PivotTables("Pivottabel3").PivotCache.Refresh



Application.EnableEvents = True


Application.ScreenUpdating = True


End Sub


Sub Makro4()


'Indsætter ****tid og udregner opdateringstid


Application.Worksheets("T6").Range("B1") = Format(Now(), "HH:MM:SS")


Application.Worksheets("T6").Range("C1") = Worksheets("T6").Range("B1") - Worksheets("T6").Range("A1")


End Sub


Sub Makro5()


'Oprettelse af meddelsesboks


Set pvtTable = Worksheets("T6").PivotTables("Pivottabel3")


a = Format(Now(), "HH:MM:SS")


b = Format(pvtTable.RefreshDate, "Long Date")


c = Format(pvtTable.RefreshName)


MsgBox "Pivottabellerne blev opdateret kl. " & a & " d. " & b & " af " & c & "." & " Opdateringen tog: " & Format(Worksheets("T6").Range("C1").Value, "HH:MM:SS" & ".")


End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
try
Code: 
Option Explicit

Sub RunAll()
    Makro1
End Sub
Sub Makro1()
    Dim a, b, c
    a = Format(Date - 1, "DD/MM/YYYY")
    'Indsætter starttid for opdateringen

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    With Sheets("T1")
        .Range("A1") = "Overblik over forløb på områder, unikke borgere visiteret pr. " & a & " - " & "inkl. Plejeboliger"
        .Range("A23") = "Overblik over forløb på områder, unikke borgere visiteret pr. " & a & " - " & "ekskl. Plejeboliger"
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
        .PivotTables("Pivottabel1").CubeFields(47).EnableMultiplePageItems = True
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")
        .PivotTables("Pivottabel1").PivotCache.Refresh
        .PivotTables("Pivottabel2").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
        .PivotTables("Pivottabel2").CubeFields(47).EnableMultiplePageItems = True
        .PivotTables("Pivottabel2").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel2").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel2").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")
        .PivotTables("Pivottabel2").PivotCache.Refresh
    End With

    With Sheets("T2")
        .PivotTables("Pivottabel1").PivotCache.Refresh
        .PivotTables("Pivottabel3").PivotCache.Refresh
    End With

    With Sheets("T3")
        .PivotTables("Pivottabel1").PivotCache.Refresh
    End With

    With Sheets("T4")
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
        .PivotTables("Pivottabel1").CubeFields(47).EnableMultiplePageItems = True
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")
        .PivotTables("Pivottabel1").PivotCache.Refresh
    End With

    With Sheets("T5")
        .PivotTables("Pivottabel1").PivotFields("[Dato].[Aar Maaned Dag].[Aar Maaned Dag]").ClearAllFilters
        .PivotTables("Pivottabel1").CubeFields(231).EnableMultiplePageItems = True
        .PivotTables("Pivottabel1").PivotFields("[Dato].[Aar Maaned Dag].[Aar Maaned Dag]").VisibleItemsList = Array("[Dato].[Aar Maaned Dag].&[20150725]")
        .PivotTables("Pivottabel1").PivotCache.Refresh
    End With

    With Sheets("T6")
        .Range("A1") = Format(Now(), "HH:MM:SS")
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
        .PivotTables("Pivottabel1").CubeFields(47).EnableMultiplePageItems = True
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel1").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")
        .PivotTables("Pivottabel1").PivotCache.Refresh
        .PivotTables("Pivottabel2").PivotCache.Refresh
        .PivotTables("Pivottabel3").PivotFields("[Dato].[AarMaanedDagH].[Aar]").ClearAllFilters
        .PivotTables("Pivottabel3").CubeFields(47).EnableMultiplePageItems = True
        .PivotTables("Pivottabel3").PivotFields("[Dato].[AarMaanedDagH].[Aar]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel3").PivotFields("[Dato].[AarMaanedDagH].[AarMd]").VisibleItemsList = Array("")
        .PivotTables("Pivottabel3").PivotFields("[Dato].[AarMaanedDagH].[AarMaanedDag]").VisibleItemsList = Array("[Dato].[AarMaanedDagH].[AarMaanedDag].&[20150725]")
        .PivotTables("Pivottabel3").PivotCache.Refresh
        .Range("B1") = Format(Now(), "HH:MM:SS")
        .Range("C1") = Worksheets("T6").Range("B1") - Worksheets("T6").Range("A1")
    End With

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set pvtTable = Worksheets("T6").PivotTables("Pivottabel3")
    a = Format(Now(), "HH:MM:SS")
    b = Format(pvtTable.RefreshDate, "Long Date")
    c = Format(pvtTable.RefreshName)
    MsgBox "Pivottabellerne blev opdateret kl. " & a & " d. " & b & " af " & c & "." & " Opdateringen tog: " & Format(Worksheets("T6").Range("C1").Value, "HH:MM:SS" & ".")

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA, OLAP cube pivot : refer to parent of selected filter?
Replies
1
Views
279
srgexcel
S
S
  • Question
Keep getting an "Error 1004: item could not be found in the OLAP cube" when applying the filter to pivot table
Replies
5
Views
909
RoryA
RoryA
Gabriel222
  • Question
Getting “Type Mismatch” with VBA PivotTables and Filter Dates
Replies
0
Views
1K
Gabriel222
Gabriel222
H
  • Question
Change date in a OLAP Cube based pivot table
Replies
1
Views
2K
Hans10
H
E
  • Question
Olap filter on dates using VBA and custom Date filter
Replies
0
Views
3K
EhhMikey
E

Forum statistics

Threads
1,214,594
Messages
6,120,436
Members
448,964
Latest member
Danni317

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top