Scribd
Upload
172 views16 pages

Microsoft Visual Basic For Applications

1) The document contains code for macros that populate dropdown lists on a worksheet based on data on other sheets and refresh charts when dropdown selections are changed. 2) The macros filter data, extract values from cells to populate dropdowns, and call subroutines to get chart data and refresh charts. 3) Selections from five dropdowns trigger queries to retrieve and display chart data from different sheets and tables based on the selections.

Uploaded by

AliKaiser
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
172 views16 pages

Microsoft Visual Basic For Applications

1) The document contains code for macros that populate dropdown lists on a worksheet based on data on other sheets and refresh charts when dropdown selections are changed. 2) The macros filter data, extract values from cells to populate dropdowns, and call subroutines to get chart data and refresh charts. 3) Selections from five dropdowns trigger queries to retrieve and display chart data from different sheets and tables based on the selections.

Uploaded by

AliKaiser
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 16

Sheet1 - 1

Private Sub Worksheet_Change(ByVal Target As Range)


Select Case [Link]
Case "$H$4"
filtersitelevel
Range("H4").Select
End Select

End Sub
Sheet12 - 1

Private Sub ComboBox1_Change()


[Link] = False
Range("B3").Value = [Link] & " " & [Link] & " 4G Performance KVSwap Celcom"

Dim targetsheet As String, vendorname As String


Dim colobject As String, colvendor As String, coldate As String, colintegrity As String
Dim endrow As Integer

If Trim([Link]) <> "" And Trim([Link]) <> "" Then


Range("AR2").Value = [Link]
Range("AS2").Value = [Link]

targetsheet = Range("AT2").Value
vendorname = Range("AU2").Value
colobject = Range("AV2").Value
colvendor = Range("AW2").Value
coldate = Range("AX2").Value
colintegrity = Range("AY2").Value

Call refreshDataCombo(targetsheet, vendorname, colobject, colvendor, coldate)

[Link]
[Link]
[Link]

Sheets("DASHBOARD").Select
'fill for object
Range("BA1").Select
[Link](xlDown).Select

If Trim(Range("BA2").Value) <> "" Then


For I = 2 To [Link]
[Link] (Range("BA" & I).Value)
Next I
End If

'fill for date


Range("BB1").Select
[Link](xlDown).Select

If Trim(Range("BB2").Value) <> "" Then


For I = 2 To [Link]
[Link] (Range("BB" & I).Text)
[Link] (Range("BB" & I).Text)
Next I
End If

End If

Range("A1").Select

[Link] = True
End Sub

Private Sub ComboBox2_Change()


[Link] = False
Range("B3").Value = [Link] & " " & [Link] & " 4G Performance KVSwap Celcom"

Dim targetsheet As String, vendorname As String


Dim colobject As String, colvendor As String, coldate As String, colintegrity As String, timelevel As
String
Dim endrow As Integer

Dim objectname As String, startdate As Date, enddate As Date

If Trim([Link]) <> "" And Trim([Link]) <> "" Then


Range("AR2").Value = [Link]
Range("AS2").Value = [Link]

targetsheet = Range("AT2").Value
vendorname = Range("AU2").Value
colobject = Range("AV2").Value
colvendor = Range("AW2").Value
Sheet12 - 2

coldate = Range("AX2").Value
colintegrity = Range("AY2").Value

Call refreshDataCombo(targetsheet, vendorname, colobject, colvendor, coldate)

[Link]
[Link]
[Link]

Sheets("DASHBOARD").Select
'fill for object
Range("BA1").Select
[Link](xlDown).Select

If Trim(Range("BA2").Value) <> "" Then


For I = 2 To [Link]
[Link] (Range("BA" & I).Value)
Next I
End If

'fill for date


Range("BB1").Select
[Link](xlDown).Select

If Trim(Range("BB2").Value) <> "" Then


For I = 2 To [Link]
[Link] (Range("BB" & I).Text)
[Link] (Range("BB" & I).Text)
Next I
End If

If Trim([Link]) <> "" And Trim([Link]) <> "" And Trim([Link]) <> "" The
n

targetsheet = Range("AT2").Value
vendorname = Range("AU2").Value
colobject = Range("AV2").Value
colvendor = Range("AW2").Value
coldate = Range("AX2").Value
colintegrity = Range("AY2").Value

timelevel = [Link]
objectname = [Link]
startdate = [Link]
enddate = [Link]

Call getSourceChartData(targetsheet, vendorname, colobject, colvendor, coldate, objectname, co


lintegrity, startdate, enddate, timelevel)

Call refreshChart(timelevel)

End If
End If

Range("A1").Select
[Link] = True
End Sub

Private Sub ComboBox3_Change()


[Link] = False

Dim targetsheet As String, vendorname As String


Dim colobject As String, colvendor As String, coldate As String, colintegrity As String, timelevel As
String
Dim endrow As Integer

Dim objectname As String, startdate As Date, enddate As Date

If Trim([Link]) <> "" And Trim([Link]) <> "" Then


Sheet12 - 3

targetsheet = Range("AT2").Value
vendorname = Range("AU2").Value
colobject = Range("AV2").Value
colvendor = Range("AW2").Value
coldate = Range("AX2").Value
colintegrity = Range("AY2").Value

timelevel = [Link]
objectname = [Link]
startdate = [Link]
enddate = [Link]

Call getSourceChartData(targetsheet, vendorname, colobject, colvendor, coldate, objectname, colint


egrity, startdate, enddate, timelevel)

Call refreshChart(timelevel)

End If

[Link] = True
End Sub

Private Sub ComboBox4_Change()


[Link] = False

Dim targetsheet As String, vendorname As String


Dim colobject As String, colvendor As String, coldate As String, colintegrity As String, timelevel As
String
Dim endrow As Integer

Dim objectname As String, startdate As Date, enddate As Date

If Trim([Link]) <> "" And Trim([Link]) <> "" Then

targetsheet = Range("AT2").Value
vendorname = Range("AU2").Value
colobject = Range("AV2").Value
colvendor = Range("AW2").Value
coldate = Range("AX2").Value
colintegrity = Range("AY2").Value

timelevel = [Link]
objectname = [Link]
startdate = [Link]
enddate = [Link]

Call getSourceChartData(targetsheet, vendorname, colobject, colvendor, coldate, objectname, colint


egrity, startdate, enddate, timelevel)

Call refreshChart(timelevel)

End If

[Link] = True
End Sub

Private Sub ComboBox5_Change()


[Link] = False

Dim targetsheet As String, vendorname As String


Dim colobject As String, colvendor As String, coldate As String, colintegrity As String, timelevel As
String
Dim endrow As Integer

Dim objectname As String, startdate As Date, enddate As Date

If Trim([Link]) <> "" And Trim([Link]) <> "" Then

targetsheet = Range("AT2").Value
vendorname = Range("AU2").Value
colobject = Range("AV2").Value
colvendor = Range("AW2").Value
coldate = Range("AX2").Value
Sheet12 - 4

colintegrity = Range("AY2").Value

timelevel = [Link]
objectname = [Link]
startdate = [Link]
enddate = [Link]

Call getSourceChartData(targetsheet, vendorname, colobject, colvendor, coldate, objectname, colint


egrity, startdate, enddate, timelevel)

Call refreshChart(timelevel)

End If

[Link] = True
End Sub

Private Sub CommandButton1_Click()

If Range("K176").Value <> "akanipatra@[Link]" Then


MsgBox "Error!"
Exit Sub
End If

[Link] = False

[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]

Range("AK1").Select
[Link](xlDown).Select

For I = 2 To [Link]
[Link] (Range("AK" & I).Value)
Range("AJ1").Value = I
Next I

[Link] ("Daily")

Range("A1").Select
[Link] = True
End Sub
Module1 - 1

Public MyData(300) As Variant


Public NamaBooknameIni, Bookname As String

Sub InisialisasiBooknameIni()
NamaBooknameIni = [Link]
test = Split(NamaBooknameIni, "\")
NamaBooknameIni = test(UBound(test))
End Sub

Sub Convert()
For k = 1 To 26
MyData(k) = Chr(k + 64)
Next k

numlog = 27
For k = 1 To 9
For p = 1 To 26
MyData(numlog) = Chr(k + 64) & Chr(p + 64)
numlog = numlog + 1
Next p
Next k
End Sub

Sub eaa1()
criteriaXX = [Link]
Sheets("Acceptance Daily").Select
Range("A1").Select

If Worksheets("Acceptance Daily").AutoFilterMode = True Then


Worksheets("Acceptance Daily").AutoFilterMode = False
End If

Range("A1").Select
[Link] Field:=4, Criteria1:=criteriaXX

Sheets("Chart Acceptance Daily").Select


Range("C6").Value = criteriaXX & " Daily Performance Indosat Bali"

'MsgBox "Chart has been updated"


End Sub

Sub CompileReport()
Dim endRowCluster As Integer
Dim dCluster As String, dKPI As String
Dim dCol1 As String, dCol2 As String, dCol3 As String, dCol4 As String
Dim valuenya As Double
Dim FoundRange As Range

InisialisasiBooknameIni
Convert

Dim templatename As String, templatepath As String, resultpath As String


Dim querypath As String, objectname As String
Dim dDate As String, newtemplatename As String
Dim queryname As String, targetsheet As String

'get basic info


querypath = Sheets("Basic Info").Range("A" & 2).Value
templatename = Sheets("Basic Info").Range("B" & 2).Value
templatepath = Sheets("Basic Info").Range("C" & 2).Value
resultpath = Sheets("Basic Info").Range("D" & 2).Value
dDate = Sheets("Basic Info").Range("E" & 2).Value

'compile data

For I = 1 To 4
Select Case I
Case Is = 1
Module1 - 2

queryname = "PMR_ReportDaily_SWAP_Region_Daily.xlsx"
targetsheet = "NETWORK Daily"
Case Is = 2
queryname = "PMR_ReportDaily_SWAP_BSC_Daily.xlsx"
targetsheet = "BSC Daily"
Case Is = 3
queryname = "PMR_ReportDaily_SWAP_Cluster_Daily.xlsx"
targetsheet = "Cluster Daily"
Case Is = 4
queryname = "PMR_ReportDaily_SWAP_Cell_Daily.xlsx"
targetsheet = "Cell Daily"
End Select

Sheets(targetsheet).Select

If Sheets(targetsheet).AutoFilterMode Then
Sheets(targetsheet).Range("A1").Select
[Link]
End If

NmbrBarisData = Sheets(targetsheet).Range("A1").[Link]

'If targetsheet = "Cell Daily" Or targetsheet = "Cell BH" Then


'Rows("2:" & (NmbrBarisData + 1)).Select
'[Link] Shift:=xlUp
'End If

'Sheets("Tmp 2G").Select
'[Link]

'open query result


[Link] (querypath & "\" & queryname)
Windows(queryname).Activate
Range("A1").Select
Range(Selection, [Link](xlToRight)).Select
Range(Selection, [Link](xlDown)).Select
[Link] = False 'clear clipboard
[Link]

'paste to template
Windows(NamaBooknameIni).Activate
'Sheets("Tmp 2G").Select
'Range("A1").Select
'[Link]
'[Link] Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False

Sheets(targetsheet).Select
NmbrBarisData = Sheets(targetsheet).Range("A1").[Link]

Range("A" & (NmbrBarisData + 1)).Select


'[Link]
[Link] Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

Rows((NmbrBarisData + 1) & ":" & (NmbrBarisData + 1)).Select


[Link] Shift:=xlUp
Range("A1").Select

'close query result


[Link] = False
Windows(queryname).Close False
[Link] = True

Windows(NamaBooknameIni).Activate
Next I
End Sub
Module1 - 3
Module2 - 1

Sub getSourceChartData(targetsheet As String, vendorname As String, colobject As String, _


colvendor As String, coldate As String, objectname As String, _
colintegrity As String, startdate As Date, enddate As Date, timelevel As String)

Convert
'MsgBox "test"
Sheets("Source Chart").Select

If Trim(Range("C2")) <> "" Then


NmbrBarisData = Sheets(targetsheet).Range("C1").[Link]
Range("A2:CZ" & NmbrBarisData).ClearContents
End If

Sheets(targetsheet).Select

If Sheets(targetsheet).AutoFilterMode Then
Sheets(targetsheet).Range("A1").Select
[Link]
End If

NmbrBarisData = Sheets(targetsheet).Range("C1").[Link]

'filter for object


[Link]("$A$1:$CZ$" & NmbrBarisData).AutoFilter Field:=colobject, Criteria1:= _
objectname

'filter for date


[Link]("$A$1:$CZ$" & NmbrBarisData).AutoFilter Field:=coldate, Criteria1:= _
">=" & startdate, Operator:=xlAnd, Criteria2:="<=" & enddate

'copy data to sheet source chart


NmbrBarisData = Sheets(targetsheet).Range("A1").[Link]

If timelevel = "BH" Then


'copy time
Range(MyData(coldate + 1) & "1:" & MyData(coldate + 1) & NmbrBarisData).Select
[Link] = False
[Link]

Sheets("Source Chart").Select
Range("D2").Select
[Link]

'copydate
Sheets(targetsheet).Select
Range(MyData(coldate) & "1:" & MyData(coldate) & NmbrBarisData).Select
[Link] = False
[Link]

Sheets("Source Chart").Select
Range("C2").Select
[Link]

'copy KPI
Sheets(targetsheet).Select
Range(MyData(colintegrity + 1) & "1:CZ" & NmbrBarisData).Select
[Link] = False
[Link]

Sheets("Source Chart").Select
Range("G2").Select
[Link]
Else
'copydate
Sheets(targetsheet).Select
Range(MyData(coldate) & "1:" & MyData(coldate) & NmbrBarisData).Select
[Link] = False
[Link]

Sheets("Source Chart").Select
Range("D2").Select
Module2 - 2

[Link]

Range("C2").Select
[Link]

'copy KPI
Sheets(targetsheet).Select
Range(MyData(colintegrity + 1) & "1:CZ" & NmbrBarisData).Select
[Link] = False
[Link]

Sheets("Source Chart").Select
Range("G2").Select
[Link]
End If

'copy object
Sheets(targetsheet).Select
Range(MyData(colobject) & "1:" & MyData(colobject) & NmbrBarisData).Select
[Link] = False
[Link]

Sheets("Source Chart").Select
Range("F2").Select
[Link]

'copy vendor
Sheets(targetsheet).Select
Range(MyData(colvendor) & "1:" & MyData(colvendor) & NmbrBarisData).Select
[Link] = False
[Link]

Sheets("Source Chart").Select
Range("E2").Select
[Link]

'ilangin header
Rows("2:2").Select
[Link] = False
[Link] Shift:=xlUp
Range("A1").Select

'check before or after


NmbrBarisData = Range("C1").[Link]

Range("A2").Select
[Link] = "=IF(E2=""HW"",""After"",""Before"")"
Range("A2").Copy
Range("A2:A" & NmbrBarisData).Select
[Link]
[Link] = False
Range("A2:A" & NmbrBarisData).Copy
Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False

'script untuk merubah yang sama jadi kosong


[Link] = False

Range("B2").FormulaLocal = "=IF(A2<>A1,A2,"""")"
Range("B2").Copy
Range("B2:B" & NmbrBarisData).Select
[Link]
[Link] = False
Range("B2:B" & NmbrBarisData).Copy
Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
:=False, Transpose:=False
Module2 - 3

Range("A1").Value = "Bef/Aft Full"


Range("B1").Value = "Bef/Aft"

If timelevel <> "BH" Then


Range("B2:B" & NmbrBarisData).Select
[Link]
Range("C2").Select
[Link]
[Link] = False

Range("C1").Value = "Bef/Aft2"
Range("D1").Value = "Date"
Else
Range("C1").Value = "Date"
Range("D1").Value = "Time"
End If

Sheets("DASHBOARD").Select

'Sheets(targetsheet).Select
'If Sheets(targetsheet).AutoFilterMode Then
' Sheets(targetsheet).Range("A1").Select
' [Link]
'End If

If Range("AR2").Value = "Trend Cluster" Then


Sheets("Source Chart").Select
Range("F2").Select
[Link]
Sheets("Summary Cluster").Select
Range("AQ1").Select
[Link]

End If

End Sub

Sub refreshChart(timelevel As String)

Sheets("Source Chart").Select
NmbrBarisData = Sheets("Source Chart").Range("C1").[Link]

If timelevel = "BH" Then


awalan = "B"
Else
awalan = "C"
End If

Sheets("DASHBOARD").Activate

'Chart 1 --> Data CSSR(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$H$1:$H$668
[Link]("Chart 1").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",G1:G" & NmbrBarisData & ",AR1:AR" & NmbrBarisData & ",BA1:B
A" & NmbrBarisData)

'Chart 2 --> RRC SSR(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$I$1:$I$668
[Link]("Chart 2").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",H1:H" & NmbrBarisData & ",AS1:AS" & NmbrBarisData & ",BB1:B
B" & NmbrBarisData)

'Chart 19 --> RRC Connection SSR (Service)(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$G$1:$GI$668
[Link]("Chart 19").Activate
[Link] Source:=Sheets("Source Chart").Range( _
Module2 - 4

awalan & "1:D" & NmbrBarisData & ",I1:I" & NmbrBarisData & ",BC1:BC" & NmbrBarisData)

'Chart 4 --> E-RAB Call Setup Success Rate(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$J$1:$J$668
[Link]("Chart 4").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",J1:J" & NmbrBarisData & ",AT1:AT" & NmbrBarisData & ",BD1:B
D" & NmbrBarisData)

'Chart 5 --> CSFB Preparation SR(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$K$1:$K$668
[Link]("Chart 5").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",K1:K" & NmbrBarisData & ",AU1:AU" & NmbrBarisData & ",BE1:B
E" & NmbrBarisData)

'Chart 6 --> eRAB Drop Call Rate(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$N$1:$N$668
[Link]("Chart 6").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",L1:L" & NmbrBarisData & ",AV1:AV" & NmbrBarisData & ",BF1:B
F" & NmbrBarisData)

'Chart 15 --> DL Packet Loss(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$L$1:$L$668
[Link]("Chart 15").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",M1:M" & NmbrBarisData & ",BG1:BG" & NmbrBarisData)

'Chart 10 --> UL Packet Loss(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$M$1:$M$668
[Link]("Chart 10").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",N1:N" & NmbrBarisData & ",BH1:BH" & NmbrBarisData)

'Chart 18 --> Intra-LTE Handover Success Rate(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$S$1:$S$668
[Link]("Chart 18").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",O1:O" & NmbrBarisData & ",AW1:AW" & NmbrBarisData & ",BI1:B
I" & NmbrBarisData)

'Chart 13 --> Network Availability Cell Level(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$O$1:$O$668
[Link]("Chart 13").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",P1:P" & NmbrBarisData & ",AX1:AX" & NmbrBarisData & ",BJ1:B
J" & NmbrBarisData)

'Chart 14 --> IRAT HOSR(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$P$1:$P$668
[Link]("Chart 14").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",Q1:Q" & NmbrBarisData & ",AY1:AY" & NmbrBarisData & ",BK1:B
K" & NmbrBarisData)

'Chart 11 --> IRATHO_L2W_SR(%)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$T$1:$T$668
[Link]("Chart 11").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",T1:T" & NmbrBarisData)

'Chart 7 --> Cell DL Average Throughput (Mbps)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$Q$1:$Q$668
[Link]("Chart 7").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",U1:U" & NmbrBarisData)
Module2 - 5

'Chart 17 --> Cell UL Average Throughput (Mbps)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$R$1:$R$668
[Link]("Chart 17").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",V1:V" & NmbrBarisData)

'Chart 16 --> Total DL Traffic Volume (GBytes)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$U$1:$U$668
[Link]("Chart 16").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",W1:W" & NmbrBarisData)

'Chart 8 --> Total UL Traffic Volume (GBytes)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$BD$1:$BD$668
[Link]("Chart 8").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",X1:X" & NmbrBarisData)

'Chart 20 --> Avg DL User THP (Mbps)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$V$1:$V$668
[Link]("Chart 20").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",Y1:Y" & NmbrBarisData)

'Chart 21 --> Avg UL User THP (Mbps)


'='Source Chart'!$C$1:$D$668,'Source Chart'!$W$1:$W$668
[Link]("Chart 21").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",Z1:Z" & NmbrBarisData)

'Chart 22 --> Data Traffic


'='Source Chart'!$C$1:$D$668,'Source Chart'!$Z$1:$Z$668
[Link]("Chart 22").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",AJ1:AJ" & NmbrBarisData)

'Chart 23 --> Voice Traffic


'='Source Chart'!$C$1:$D$668,'Source Chart'!$AA$1:$AA$668
[Link]("Chart 23").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",AK1:AK" & NmbrBarisData)

'Chart 24 --> UL_Resource Block Utilization


'='Source Chart'!$C$1:$D$668,'Source Chart'!$AC$1:$AC$668
[Link]("Chart 24").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",AP1:AP" & NmbrBarisData)

'Chart 25 --> DL_Resource Block Utilization


'='Source Chart'!$C$1:$D$668,'Source Chart'!$AD$1:$AD$668
[Link]("Chart 25").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",AQ1:AQ" & NmbrBarisData)

'Chart 26 --> DL_Resource Block Utilization


'='Source Chart'!$C$1:$D$668,'Source Chart'!$AC$1:$AC$668
[Link]("Chart 26").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",BM1:BM" & NmbrBarisData)

'Chart 27 --> UL_Resource Block Utilization


'='Source Chart'!$C$1:$D$668,'Source Chart'!$AD$1:$AD$668
[Link]("Chart 27").Activate
[Link] Source:=Sheets("Source Chart").Range( _
awalan & "1:D" & NmbrBarisData & ",BN1:BN" & NmbrBarisData)

End Sub

Sub refreshDataCombo(targetsheet As String, vendorname As String, colobject As String, _


Module2 - 6

colvendor As String, coldate As String)


'if vendor = all
Convert
'MsgBox "test"
Sheets("DASHBOARD").Select
Columns("BA:BB").Select
[Link]

Sheets(targetsheet).Select

If Sheets(targetsheet).AutoFilterMode Then
Sheets(targetsheet).Range("A1").Select
[Link]
End If

NmbrBarisData = Sheets(targetsheet).Range("A1").[Link]

If vendorname <> "All" Then


'MsgBox "test"
Range(MyData(colvendor) & "1").Select
[Link]
[Link]("$A$1:$CZ$" & NmbrBarisData).AutoFilter Field:=colvendor, Criteria1:=vendorname
End If

Range(MyData(colobject) & "1").Select


Range(Selection, [Link](xlDown)).Select
[Link] = False
[Link]
Sheets("DASHBOARD").Select
Range("BA1").Select
[Link]

Range("BA1").Select
[Link](xlDown).Select
endrow = [Link]

Columns("BA:BA").Select
[Link] = False
[Link]("$BA$1:$BA$" & endrow).RemoveDuplicates Columns:=1, Header:= _
xlYes
Range("BA1").Select
ActiveCell.FormulaR1C1 = "Object"
With [Link](Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("BA2").Select

Sheets(targetsheet).Select

Range(MyData(coldate) & "1").Select


Range(Selection, [Link](xlDown)).Select
[Link] = False
[Link]
Sheets("DASHBOARD").Select
Range("BB1").Select
[Link]

Range("BB1").Select
Module2 - 7

[Link](xlDown).Select
endrow = [Link]

Columns("BB:BB").Select
[Link] = False
[Link]("$BB$1:$BB$" & endrow).RemoveDuplicates Columns:=1, Header:= _
xlYes
Range("BB1").Select
ActiveCell.FormulaR1C1 = "Avail Date"
With [Link](Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("BB2").Select

'Sheets(targetsheet).Select
'If Sheets(targetsheet).AutoFilterMode Then
' Sheets(targetsheet).Range("A1").Select
' [Link]
'End If

Sheets("DASHBOARD").Select
End Sub
Module3 - 1

Sub filtersitelevel()

[Link] = False
[Link] = False

Sheets("Summary").Select

cluster = Range("H4").Value
Date = Range("G4").Value

Sheets("Pivot").Select

[Link]("PivotTable4").PivotFields("Cluster").ClearAllFilters
[Link]("PivotTable4").PivotFields("Date").ClearAllFilters
[Link]("PivotTable4").PivotFields("Cluster").CurrentPage = cluster
[Link]("PivotTable4").PivotFields("Date").CurrentPage = Date

Sheets("Pivot").Select
Range("A6").Select
Range(Selection, [Link](xlToRight)).Select
Range(Selection, [Link](xlDown)).Select
[Link] = False 'clear clipboard
[Link]

Sheets("Summary").Select
Range("AT5").Select
[Link] Paste:=xlPasteValues, Operation:=xlNone, skipblanks:=False, Transpose:=False

End Sub

You might also like