Sub PG_1_OppdatereALT()
Call KIT_1_OppdatereALT_SumSection
Call KIT_1_OppdatereALT_group1
Call KIT_1_OppdatereALT_group2
Call KIT_1_OppdatereALT_group3
Call KIT_1_OppdatereALT_group4
Call KIT_1_OppdatereALT_group5
Call KIT_1_OppdatereALT_group6
End Sub
Private Sub KIT_1_OppdatereALT_SumSection()
Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
Dim pf As PivotField
Dim pi As PivotItem
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath
Windows("01-Sum_SECTION_(M).xlsm").Activate
Sheets("NYsum1").Visible = True
Sheets("NYf1").Visible = True
Sheets("NYf2").Visible = True
Sheets("NYe").Visible = True
Sheets("NYd").Visible = True
Sheets("NYs").Visible = True
Sheets("FOsum1").Visible = True
Sheets("FOf1").Visible = True
Sheets("FOf2").Visible = True
Sheets("FOe").Visible = True
Sheets("FOd").Visible = True
Sheets("FOs").Visible = True
Sheets("Data1").Visible = True
Sheets("cn1").Visible = True
Sheets("cn2").Visible = True
Sheets("pr-sum1").Visible = True
Sheets("pr-f1").Visible = True
Sheets("pr-f2").Visible = True
Sheets("pr-e").Visible = True
Sheets("pr-s").Visible = True
Sheets("piv-a").Visible = True
Sheets("piv-b").Visible = True
Sheets("piv-c").Visible = True
Sheets("piv-d").Visible = True
Sheets("piv-e").Visible = True
Sheets("piv-f").Visible = True
Sheets("piv-g").Visible = True
Sheets("piv-h").Visible = True
Sheets("piv-i").Visible = True
Sheets("piv-j").Visible = True
Sheets("piv-k").Visible = True
Sheets("1-frv").Visible = True
Sheets("2-frh").Visible = True
Sheets("3-frk").Visible = True
Sheets("4-reu").Visible = True
Sheets("5-rem").Visible = True
Sheets("6-sk").Visible = True
Sheets("7-skb").Visible = True
Sheets("8-oth").Visible = True
Sheets("9-ohs").Visible = True
Sheets("10-td").Visible = True
Sheets("11-kmi").Visible = True
Sheets("12-ile").Visible = True
Set wb = Workbooks.Open(fPath & "\R2\" & "Wb1" & ".xls")
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYsum1").Range("A1")
wb.Close False
On Error GoTo 0
Set wb = Workbooks.Open(fPath & "\R2\" & "Wb2" & ".xls")
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYf1").Range("A1")
wb.Close False
On Error GoTo 0
Set wb = Workbooks.Open(fPath & "\R2\" & "Wb3" & ".xls")
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYf2").Range("A1")
wb.Close False
On Error GoTo 0
Set wb = Workbooks.Open(fPath & "\R2\" & "Wb4" & ".xls")
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYe").Range("A1")
wb.Close False
On Error GoTo 0
Set wb = Workbooks.Open(fPath & "\R2\" & "Wb5" & ".xls")
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYd").Range("A1")
wb.Close False
On Error GoTo 0
Set wb = Workbooks.Open(fPath & "\R2\" & "Wb6" & ".xls")
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYs").Range("A1")
wb.Close False
On Error GoTo 0
ThisWorkbook.Sheets(«Kont»).Select
foundrow = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(«SUM alle knt (kun F)», Cells(1, 1)).Row
Range("G" & foundrow & ":EO" & foundrow).Copy
ThisWorkbook.Sheets("Data1").Activate
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc1" & ".xls")
wb.Sheets(1).Activate
Range("B4:B6").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc1" & ".xls")
wb.Sheets(1).Activate
Range("C7:C7").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc2" & ".xls")
wb.Sheets(1).Activate
Range("B4:B6").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("I" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc2" & ".xls")
wb.Sheets(1).Activate
Range("C7:C7").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("L" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc3" & ".xls")
wb.Sheets(1).Activate
Range("B4:B6").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("O" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc3" & ".xls")
wb.Sheets(1).Activate
Range("C7:C7").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("R" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc4" & ".xls")
wb.Sheets(1).Activate
Range("B4:B6").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("U" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc4" & ".xls")
wb.Sheets(1).Activate
Range("C7:C7").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("X" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc5" & ".xls")
wb.Sheets(1).Activate
Range("B4:B6").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("AA" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc5" & ".xls")
wb.Sheets(1).Activate
Range("C7:C7").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("AD" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc6" & ".xls")
wb.Sheets(1).Activate
Range("B4:B6").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("AG" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc6" & ".xls")
wb.Sheets(1).Activate
Range("C7:C7").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("AJ" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc7" & ".xls")
wb.Sheets(1).Activate
Range("B4:B6").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("AM" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc7" & ".xls")
wb.Sheets(1).Activate
Range("C7:C7").Copy
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("cn2").Select
Range("AP" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
wb.Close False
Sheets("cn1").Select
Application.ScreenUpdating = False
Sheets("cn1").Range("U3:U198").Value = _
Sheets("cn2").Range("B3:B198").Value
Sheets("cn1").Range("V3:V198").Value = _
Sheets("cn2").Range("H3:H198").Value
Sheets("cn1").Range("W3:W198").Value = _
Sheets("cn2").Range("N3:N198").Value
Sheets("cn1").Range("X3:X198").Value = _
Sheets("cn2").Range("T3:T198").Value
Sheets("cn1").Range("Y3:Y198").Value = _
Sheets("cn2").Range("Z3:Z198").Value
Sheets("cn1").Range("Z3:Z198").Value = _
Sheets("cn2").Range("AF3:AF198").Value
Sheets("cn1").Range("AA3:AA198").Value = _
Sheets("cn2").Range("AL3:AL198").Value
Sheets("cn1").Select
For Each Cell In [U3:U198]
If Cell.Value = "0" Then Cell.ClearContents
Next Cell
Sheets("cn1").Range("AC3:AC198").Value = _
Sheets("cn2").Range("E3:E198").Value
Sheets("cn1").Range("AD3:AD198").Value = _
Sheets("cn2").Range("K3:K198").Value
Sheets("cn1").Range("AE3:AE198").Value = _
Sheets("cn2").Range("Q3:Q198").Value
Sheets("cn1").Range("AF3:AF198").Value = _
Sheets("cn2").Range("W3:W198").Value
Sheets("cn1").Range("AG3:AG198").Value = _
Sheets("cn2").Range("AC3:AC198").Value
Sheets("cn1").Range("AH3:AH198").Value = _
Sheets("cn2").Range("AI3:AI198").Value
Sheets("cn1").Range("AI3:AI198").Value = _
Sheets("cn2").Range("AO3:AO198").Value
Sheets("cn1").Select
For Each Cell In [AC3:AI198]
If Cell.Value = "0" Then Cell.ClearContents
Next Cell
On Error GoTo ErrorHandler
Sheets("cn1").Range("AS3:AY198").Value = _
Sheets("cn1").Range("AK3:AQ198").Value
Sheets("cn1").Select
For Each Cell In [AS3:AY198]
If Cell.Value = "0" Then
Cell.Value = Cell.Offset(-1, 0).Value
End If
Next Cell
Application.ScreenUpdating = True
Sheets(«Dash»).Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Windows(«01-Sum_SECTION_(M).xlsm»).Activate
Sheets("piv-a").Select
For Each pt In Worksheets("piv-a").PivotTables
pt.RefreshTable
Next pt
ActiveSheet.PivotTables("Pivottabell7").PivotFields(«Kon").AutoSort _
xlAscending, "Sum1"
ActiveSheet.PivotTables("Pivottabell8").PivotFields(«Kon").AutoSort _
xlAscending, "Sum2"
ActiveSheet.PivotTables("Pivottabell1").PivotFields(«Kon").AutoSort _
xlAscending, "Sum3"
Sheets("piv-i").Select
For Each pt In Worksheets("piv-a").PivotTables
pt.RefreshTable
Next pt
Sheets("piv-k").Select
For Each pt In Worksheets("piv-k").PivotTables
pt.RefreshTable
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheets("NYsum1").Visible = False
Sheets("NYf1").Visible = False
Sheets("NYf2").Visible = False
Sheets("NYe").Visible = False
Sheets("NYd").Visible = False
Sheets("NYs").Visible = False
Sheets("FOsum1").Visible = False
Sheets("FOf1").Visible = False
Sheets("FOf2").Visible = False
Sheets("FOe").Visible = False
Sheets("FOd").Visible = False
Sheets("FOs").Visible = False
Sheets("Data1").Visible = False
Sheets("cn1").Visible = False
Sheets("cn2").Visible = False
Sheets("pr-sum1").Visible = False
Sheets("pr-f1").Visible = False
Sheets("pr-f2").Visible = False
Sheets("pr-e").Visible = False
Sheets("pr-s").Visible = False
Sheets("piv-a").Visible = False
Sheets("piv-b").Visible = False
Sheets("piv-c").Visible = False
Sheets("piv-d").Visible = False
Sheets("piv-e").Visible = False
Sheets("piv-f").Visible = False
Sheets("piv-g").Visible = False
Sheets("piv-h").Visible = False
Sheets("piv-i").Visible = False
Sheets("piv-j").Visible = False
Sheets("piv-k").Visible = False
Sheets("1-frv").Visible = False
Sheets("2-frh").Visible = False
Sheets("3-frk").Visible = False
Sheets("4-reu").Visible = False
Sheets("5-rem").Visible = False
Sheets("6-sk").Visible = False
Sheets("7-skb").Visible = False
Sheets("8-oth").Visible = False
Sheets("9-ohs").Visible = False
Sheets("10-td").Visible = False
Sheets("11-kmi").Visible = False
Sheets("12-ile").Visible = False
ThisWorkbook.Sheets(«Dash»).Select
Range("A1").Select
ActiveWorkbook.Save
Exit Sub
ErrorHandler:
End Sub
Private Sub KIT_1_OppdatereALT_group1()
… code is pretty much similar for each «private bit»…