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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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 Question
VBA, OLAP cube pivot : refer to parent of selected filter?
Replies
1
Views
523
srgexcel
S
S
  • Question 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
1K
RoryA
RoryA
Gabriel222
  • Question Question
Getting “Type Mismatch” with VBA PivotTables and Filter Dates
Replies
0
Views
2K
Gabriel222
Gabriel222
H
  • Question Question
Change date in a OLAP Cube based pivot table
Replies
1
Views
3K
Hans10
H
E
  • Question Question
Olap filter on dates using VBA and custom Date filter
Replies
0
Views
3K
EhhMikey
E

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

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