Private Sub Samenvatting_Click()
' Maakt de sheet Exclusive
If ActiveWorkbook.MultiUserEditing Then
Application.DisplayAlerts = False
ActiveWorkbook.ExclusiveAccess
Application.DisplayAlerts = True
End If
' Delete de oude lijst en voegt nieuwe lijnen in
Sheets("Alle CCF").Select
Rows("7:" & Range("BM1") + 3).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Application.Goto Reference:="R7C1:R400C1"
Selection.RowHeight = 20
Range("A1").Select
' Verzamelt alle sheets met een update van de formules
Application.ScreenUpdating = False
SheetNames = Array("Apothekers", "Kinesisten", "MedGen", "Tandartsen", "Specialisten")
With Sheets("Alle CCF")
.Range(.Range("CZ6"), .Cells(.Rows.Count, 1).End(xlUp)).ClearContents
End With
For i = LBound(SheetNames) To UBound(SheetNames)
With Sheets(SheetNames(i))
.Range(.Range("CZ6"), .Cells(.UsedRange.Rows.Count, 1)).Copy Sheets("Alle CCF").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
Next i
Application.ScreenUpdating = True
Range("A1000000").End(xlUp).Offset(2).End(xlToRight).Select
' Update alle formules
ActiveCell = "=SUM(R6C:R[-2]C)"
ActiveCell.Offset(, 1).Select
For keer = 1 To 4
ActiveCell = "=(SUMIF(R6C19:R[-2]C19,""UW"",R6C:R[-2]C)+(SUMIF(R6C19:R[-2]C19,""WMU"",R6C:R[-2]C)))"
ActiveCell.Offset(1).Select
ActiveCell = "=(SUMIF(R6C19:R[-3]C19,""WM"",R6C:R[-3]C)+(SUMIF(R6C19:R[-3]C19,""WJ"",R6C:R[-3]C)))"
ActiveCell.Offset(-1, 1).Select
ActiveCell = "=SUM(R6C:R[-2]C)"
ActiveCell.Offset(, 1).Select
ActiveCell = "=(SUMIF(R6C19:R[-2]C19,""UW"",R6C:R[-2]C)+(SUMIF(R6C19:R[-2]C19,""WMU"",R6C:R[-2]C)))"
ActiveCell.Offset(1).Select
ActiveCell = "=(SUMIF(R6C19:R[-3]C19,""WM"",R6C:R[-3]C)+(SUMIF(R6C19:R[-3]C19,""WJ"",R6C:R[-3]C)))"
ActiveCell.Offset(-1, 1).Select
ActiveCell = "=SUM(R6C:R[-2]C)"
ActiveCell.Offset(, 1).Select
ActiveCell = "=(SUMIF(R6C19:R[-2]C19,""UW"",R6C:R[-2]C)+(SUMIF(R6C19:R[-2]C19,""WMU"",R6C:R[-2]C)))"
ActiveCell.Offset(1).Select
ActiveCell = "=(SUMIF(R6C19:R[-3]C19,""WM"",R6C:R[-3]C)+(SUMIF(R6C19:R[-3]C19,""WJ"",R6C:R[-3]C)))"
ActiveCell.Offset(-1, 1).Select
ActiveCell = "=SUM(R6C:R[-2]C)"
ActiveCell.Offset(, 2).Select
Next keer
ActiveCell.Offset(, -1).Select
ActiveCell = "=(SUMIF(R6C19:R[-2]C19,""UW"",R6C:R[-2]C)+(SUMIF(R6C19:R[-2]C19,""WMU"",R6C:R[-2]C55)))"
ActiveCell.Offset(1).Select
ActiveCell = "=(SUMIF(R6C19:R[-3]C19,""WM"",R6C:R[-3]C)+(SUMIF(R6C19:R[-3]C19,""WJ"",R6C:R[-3]C)))"
ActiveCell.Offset(-1, 3).Select
ActiveCell = "=SUM(R6C:R[-2]C)"
ActiveCell.Offset(, 1).Select
ActiveCell = "=SUM(R6C:R[-2]C)"
ActiveCell.Offset(, 1).Select
ActiveCell = "=SUM(R6C:R[-2]C)"
ActiveCell.Offset(, 1).Select
ActiveCell = "=SUM(R6C:R[-2]C)"
ActiveCell.Offset(4, -3).Select
ActiveCell = "=SUMIF(R6C5:R[-6]C5,RC[-1],R6C:R[-6]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUMIF(R6C5:R[-7]C5,RC[-1],R6C:R[-7]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUMIF(R6C5:R[-8]C5,RC[-1],R6C:R[-8]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=((R[-1]C)-(R[-8]C))"
ActiveCell.Offset(-4, 1).Select
ActiveCell = "=SUMIF(R6C5:R[-6]C5,RC[-2],R6C:R[-6]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUMIF(R6C5:R[-7]C5,RC[-2],R6C:R[-7]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUMIF(R6C5:R[-8]C5,RC[-2],R6C:R[-8]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=((R[-1]C)-(R[-8]C))"
ActiveCell.Offset(-4, 1).Select
ActiveCell = "=SUMIF(R6C5:R[-6]C5,RC[-3],R6C:R[-6]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUMIF(R6C5:R[-7]C5,RC[-3],R6C:R[-7]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUMIF(R6C5:R[-8]C5,RC[-3],R6C:R[-8]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUM(R[-4]C:R[-1]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=((R[-1]C)-(R[-8]C))"
ActiveCell.Offset(-4, 1).Select
ActiveCell = "=SUMIF(R6C5:R[-6]C5,RC[-4],R6C:R[-6]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUMIF(R6C5:R[-7]C5,RC[-4],R6C:R[-7]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUMIF(R6C5:R[-8]C5,RC[-4],R6C:R[-8]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=SUM(R[-4]C:R[-1]C)"
ActiveCell.Offset(1).Select
ActiveCell = "=((R[-1]C)-(R[-8]C))"
ActiveCell.Offset(-5).Select
Selection.Rows.AutoFit
Columns("A:BL").Select
Selection.Columns.AutoFit
Columns("BJ:BK").Select
Selection.Columns.AutoFit
Range("BM1") = "=COUNTA(R6C1:R1000000C1)+5"
Range("BN1") = "=""$A$6:$A$""&RC[-1]"
Range("B2") = "=IF(ISERROR(SUMPRODUCT(1/COUNTIF(INDIRECT(R[-1]C[+64]),INDIRECT(R[-1]C[+64])))),0,(SUMPRODUCT(1/COUNTIF(INDIRECT(R[-1]C[+64]),INDIRECT(R[-1]C[+64])))))"
Columns("BM:CZ").Select
Selection.EntireColumn.Hidden = True
ActiveWorkbook.Worksheets("Alle CCF").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Alle CCF").Sort.SortFields.Add Key:=Range("A5"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Alle CCF").Sort
.SetRange Range("A6:BK" & Range("BM1"))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A3").Select
Sheets("DT Alle CCF").Select
' Range("A4").Select
Regels = [BM1]
ActiveSheet.PivotTables("Draaitabel1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Alle CCF!R5C1:R" & Regels & "C63" _
, Version:=xlPivotTableVersion14)
ActiveWorkbook.RefreshAll
Sheets("Alle CCF").Select
Range("A3").Select
If Not ActiveWorkbook.MultiUserEditing Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ActiveWorkbook.Name, accessmode:=xlShared
Application.DisplayAlerts = True
End If
End Sub