Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2018-06-11 10:21 AM

Unknown Runtime Error on .Interior.Color = RGB(255,192,0) on macro

Hello. I have moved the QlikView app that I was developing from my workstation to a remote server. All the connections are well(the data is loaded as intended) and the client installed Office 365 on the machine that I'm supposed to work on. Now the problem: I'm testing the export macros that I'm using to generate some reports, but I keep getting an Unknown Runtime Error on:
XLDoc.Sheets(i+1).Range("C1,E1,G1,I1,K1,O1,Q1,M1,S1,C2,E2,G2,I2,K2,O2,Q2,M2,S2").Interior.Color = RGB(255,192,0)
Is there any way I can find out more about why I keep getting that error or can anyone help me figure out why this error keeps getting thrown? Here's the full macro:

sub CustomExcelReportMacro

Dim ZoneAvgArray(8)

Dim MediaItalia(8)

Dim cicle_value

set XLApp = CreateObject("Excel.Application")

set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets(1).Name ="Italia"

XlDoc.Sheets(2).Name = "NordOvest"

XlDoc.Sheets(3).Name = "NordEst"

XlDoc.Sheets(4).Name = "Centro"

XlDoc.Sheets(5).Name = "Sud"

XlDoc.Sheets(6).Name = "Isole"

XlDoc.Sheets(7).Name = "TuttiISF"

XLDoc.Sheets(10).Delete

XLDoc.Sheets(9).Delete

XLDoc.Sheets(8).Delete

aSheetObj = Array("CH172","CH170","CH169","CH168","CH166","CH167","CH171")

'Pastes the tables'

for i=0 to UBound(aSheetObj)

XLDoc.Sheets(i+1).Select

ActiveDocument.GetSheetObject(aSheetObj(i)).CopyTableToClipboard true

XLDoc.Sheets(i+1).Range("A1").Select

XLDoc.Sheets(i+1).Paste()

XLDoc.Sheets(i+1).Rows("1:3000").EntireRow.AutoFit

'Yellow'

XLDoc.Sheets(i+1).Range("A1:S1").Interior.Color = RGB(255,255,0)

next

XLDoc.Sheets(1).Range("A2").EntireRow.Insert

cicle_value = XLDoc.Sheets(1).Range("B3").Value

Select case cicle_value

case 77

  XLDoc.Sheets(1).Range("A1") = "Ciclo 1"

case 93

  XLDoc.Sheets(1).Range("A1") = "Ciclo 2"

case 82

  XLDoc.Sheets(1).Range("A1") = "Ciclo 3"

case else

  XLDoc.Sheets(1).Range("A1") = "Anno"

end select

'1)Calculates the area average'

for i = 1 to UBound(aSheetObj)

XLDoc.Sheets(i+1).Range("A2").EntireRow.Insert

XLDoc.Sheets(i+1).Range("A2").Value = "Media Area"

XLDoc.Sheets(i+1).Range("C2").Formula = "=AVERAGE(R[1]C:R[40]C)"

XLDoc.Sheets(i+1).Range("E2").Formula = "=AVERAGE(R[1]C:R[40]C)"

XLDoc.Sheets(i+1).Range("G2").Formula = "=AVERAGE(R[1]C:R[40]C)"

XLDoc.Sheets(i+1).Range("I2").Formula = "=AVERAGE(R[1]C:R[40]C)"

XLDoc.Sheets(i+1).Range("K2").Formula = "=AVERAGE(R[1]C:R[40]C)"

XLDoc.Sheets(i+1).Range("M2").Formula = "=AVERAGE(R[1]C:R[40]C)"

XLDoc.Sheets(i+1).Range("O2").Formula = "=AVERAGE(R[1]C:R[40]C)"

XLDoc.Sheets(i+1).Range("Q2").Formula = "=AVERAGE(R[1]C:R[40]C)"

XLDoc.Sheets(i+1).Range("S2").Formula = "=AVERAGE(R[1]C:R[40]C)"

XLDoc.Sheets(i+1).Range("A1:S20").HorizontalAlignment = -4152

'Orange'

XLDoc.Sheets(i+1).Range("C1,E1,G1,I1,K1,O1,Q1,M1,S1,C2,E2,G2,I2,K2,O2,Q2,M2,S2").Interior.Color = RGB(255,192,0)

'gets the Zone Average and puts it in an array'

ZoneAvgArray(0) = XLDoc.Sheets(i+1).Range("E2").Value

ZoneAvgArray(1) = XLDoc.Sheets(i+1).Range("G2").Value

ZoneAvgArray(2) = XLDoc.Sheets(i+1).Range("I2").Value

ZoneAvgArray(3) = XLDoc.Sheets(i+1).Range("K2").Value

ZoneAvgArray(4) = XLDoc.Sheets(i+1).Range("M2").Value

ZoneAvgArray(5) = XLDoc.Sheets(i+1).Range("O2").Value

ZoneAvgArray(6) = XLDoc.Sheets(i+1).Range("Q2").Value

ZoneAvgArray(7) = XLDoc.Sheets(i+1).Range("S2").Value

XLDoc.Sheets(i+1).Range("D2:S11").NumberFormat = "0.00"

XLDoc.Sheets(1).Range("D" & i + 2).Value = ZoneAvgArray(0)

XLDoc.Sheets(1).Range("F" & i + 2).Value = ZoneAvgArray(1)

XLDoc.Sheets(1).Range("H" & i + 2).Value = ZoneAvgArray(2)

XLDoc.Sheets(1).Range("J" & i + 2).Value = ZoneAvgArray(3)

XLDoc.Sheets(1).Range("L" & i + 2).Value = ZoneAvgArray(4)

XLDoc.Sheets(1).Range("N" & i + 2).Value = ZoneAvgArray(5)   

XLDoc.Sheets(1).Range("P" & i + 2).Value = ZoneAvgArray(6) 

XlDoc.Sheets(1).Range("R" & i + 2).Value = ZoneAvgArray(7)

XlDoc.Sheets(1).Range("S" & i + 2).Value = ZoneAvgArray(8)

next

'3) calculates averages in Media Italia'

XLDoc.Sheets(1).Range("B2").Formula = "=AVERAGE(R[1]C:R[40]C)"

MediaItalia(0) = XLDoc.Sheets(1).Range("B2").Value

XLDoc.Sheets(1).Range("D2").Formula = "=AVERAGE(R[1]C:R[40]C)"

MediaItalia(1) = XLDoc.Sheets(1).Range("D2").Value

 

XLDoc.Sheets(1).Range("F2").Formula = "=AVERAGE(R[1]C:R[40]C)"

MediaItalia(2) = XLDoc.Sheets(1).Range("F2").Value

 

XLDoc.Sheets(1).Range("H2").Formula = "=AVERAGE(R[1]C:R[40]C)"

MediaItalia(3) = XLDoc.Sheets(1).Range("H2").Value

 

XLDoc.Sheets(1).Range("J2").Formula = "=AVERAGE(R[1]C:R[40]C)"

MediaItalia(4) = XLDoc.Sheets(1).Range("J2").Value

 

XLDoc.Sheets(1).Range("L2").Formula = "=AVERAGE(R[1]C:R[40]C)"

MediaItalia(5) = XLDoc.Sheets(1).Range("L2").Value

 

XLDoc.Sheets(1).Range("N2").Formula = "=AVERAGE(R[1]C:R[40]C)"

MediaItalia(6) = XLDoc.Sheets(1).Range("N2").Value

 

XLDoc.Sheets(1).Range("P2").Formula = "=AVERAGE(R[1]C:R[40]C)"

MediaItalia(7) = XLDoc.Sheets(1).Range("P2").Value

XLDoc.Sheets(1).Range("R2").Formula = "=AVERAGE(R[1]C:R[40]C)"

MediaItalia(8) = XLDoc.Sheets(1).Range("R2").Value

 

XLDoc.Sheets(1).Range("A2").Value = "Media Italia"

XLDoc.Sheets(1).Range("A1:S40").HorizontalAlignment = -4152

XLDoc.Sheets(1).Range("C2:C3").NumberFormat = "0.00"

XLDoc.Sheets(1).Range("B2:S7").NumberFormat = "0.00"

'Orange'

XLDoc.Sheets(1).Range("B1,D1,F1,H1,J1,L1,N1,P1,R1,B2,D2,F2,H2,J2,L2,N2,P2,R2").Interior.Color = RGB(255,192,0)

XLDoc.Sheets(1).Range("S1,S2").Interior.Color = RGB(255,255,255)

'4)Pastes the values from Media Italia'

for i=1 to UBound(aSheetObj)

XLDoc.Sheets(i+1).Range("A2").EntireRow.Insert

XLDoc.Sheets(i+1).Range("A2").Value = "Media Italia"

XLDoc.Sheets(i+1).Range("C2").Value = MediaItalia(0)

XLDoc.Sheets(i+1).Range("E2").Value = MediaItalia(1)

XLDoc.Sheets(i+1).Range("G2").Value = MediaItalia(2)

XLDoc.Sheets(i+1).Range("I2").Value = MediaItalia(3)

XLDoc.Sheets(i+1).Range("K2").Value = MediaItalia(4)

XLDoc.Sheets(i+1).Range("M2").Value = MediaItalia(5)

XLDoc.Sheets(i+1).Range("O2").Value = MediaItalia(6)

XLDoc.Sheets(i+1).Range("Q2").Value = MediaItalia(7)

XLDoc.Sheets(i+1).Range("S2").Value = MediaItalia(8)

XLDoc.Sheets(i+1).Range("D2:T2").NumberFormat = "0.00"

next

XLDoc.Sheets(7).Range("A3").EntireRow.Clear

XLDoc.Sheets(7).Range("A3").EntireRow.Delete

XLDoc.Sheets(1).Range("D8").EntireRow.Clear

XLApp.Visible = true

end sub

sub ExcelReport4Custom

set XLApp = CreateObject("Excel.Application")

'XLApp.Visible = True

set XLDoc = XLApp.Workbooks.Add

TableArray = Array("CH185","CH190","CH187","CH191","CH188","CH192","CH174","CH193","CH186","CH194","CH189","CH195")

Dim NoOfRows1

Dim NoOfRows2

XLDoc.Sheets(1).Name = "Italia"

XLDoc.Sheets(2).Name = "Italia Visite"

XLDoc.Sheets(3).Name = "Nord ovest"

XLDoc.Sheets(4).Name = "Nord Est"

XLDoc.Sheets(5).Name = "Centro"

XLDoc.Sheets(6).Name = "Sud"

XLDoc.Sheets(7).Name = "Isole e Calabria"

'cleaning up the garbage'

XLDoc.Sheets(10).Delete

XLDoc.Sheets(9).Delete

XLDoc.Sheets(8).Delete

'Pasting tables....'

'First Sheet - Italia'

'Paste the table along with new headers'

XLDoc.Sheets(1).Select

XLDoc.Sheets(1).Range("D1:J1").HorizontalAlignment = -4108

XLDoc.Sheets(1).Range("D1:J1").Merge

XLDoc.Sheets(1).Range("D1").Value = "Media Visite Ciclo1"

XLDoc.Sheets(1).Range("D1").Font.Bold = true

XLDoc.Sheets(1).Range("L1:R1").HorizontalAlignment = -4108

XLDoc.Sheets(1).Range("L1:R1").Merge

XLDoc.Sheets(1).Range("L1").Value = "Media Visite Ciclo2"

XLDoc.Sheets(1).Range("L1").Font.Bold = true

XLDoc.Sheets(1).Range("T1:Z1").HorizontalAlignment = -4108

XLDoc.Sheets(1).Range("T1:Z1").Merge

XLDoc.Sheets(1).Range("T1").Value = "Media Visite Ciclo3"

XLDoc.Sheets(1).Range("T1").Font.Bold = true

XLDoc.Sheets(1).Range("AB11:AH1").HorizontalAlignment = -4108

XLDoc.Sheets(1).Range("AB1:AH1").Merge

XLDoc.Sheets(1).Range("AB1").Value = "Media Visite Anuale"

XLDoc.Sheets(1).Range("AB1").Font.Bold = true

ActiveDocument.GetSheetObject(TableArray(0)).CopyTableToClipboard true

XLDoc.Sheets(1).Range("A2").Select

XLDoc.Sheets(1).Paste()

'find the number of records in the pasted table'

XLDoc.Sheets(1).Range("AI1").Formula = "=COUNT(R[1]C[-32]:R[498]C[-32])"

NoOfRows1 = XLDoc.Sheets(1).Range("AI1").Value + 2

XLDoc.Sheets(1).Range("AI1").Clear

'Section to delete once the client changes his mind'

XLDoc.Sheets(1).Range("C3:AH3").Clear

XLDoc.Sheets(1).Range("C3:AH3").Formula = "=Average(R[1]C:R[397]C)"

XLDoc.Sheets(1).Range("C3:AH3").NumberFormat = "0.00"

'End section'

'Lines for headers'

'Horizontal lines'

XLDoc.Sheets(1).Range("A3:AH3").Borders.LineStyle = xlContinuous

XLDoc.Sheets(1).Range("A3:AH3").Borders(9).Weight = 4

XLDoc.Sheets(1).Range("A3:AH3").Borders(8).Weight = 4

XLDoc.Sheets(1).Range("A3:AH3").Borders(9).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range("A3:AH3").Borders(8).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range("A" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders.LineStyle = xlContinuous

XLDoc.Sheets(1).Range("A" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(9).Weight = 4

XLDoc.Sheets(1).Range("A" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(9).Color = RGB(0,0,0)

'Vertical lines'

XLDoc.Sheets(1).Range( "B" & "1" & ":" & "B" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(1).Range( "B" & "1" & ":" & "B" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range( "C" & "1" & ":" & "C" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(1).Range( "C" & "1" & ":" & "C" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range( "J" & "1" & ":" & "J" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(1).Range( "J" & "1" & ":" & "J" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range( "K" & "1" & ":" & "K" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(1).Range( "K" & "1" & ":" & "K" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range( "R" & "1" & ":" & "R" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(1).Range( "R" & "1" & ":" & "R" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range( "S" & "1" & ":" & "S" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(1).Range( "S" & "1" & ":" & "S" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range( "Z" & "1" & ":" & "Z" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(1).Range( "Z" & "1" & ":" & "Z" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range( "AA" & "1" & ":" & "AA" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(1).Range( "AA" & "1" & ":" & "AA" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(1).Range( "AH" & "1" & ":" & "AH" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(1).Range( "AH" & "1" & ":" & "AH" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

'Yellow'

XLDoc.Sheets(1).Range("A" & "1" & ":" & "B" & NoOfRows1).Interior.Color = RGB(255,255,0)

XLDoc.Sheets(1).Range("K" & "1" & ":" & "R" & 2).Interior.Color = RGB(255,255,0)

XLDoc.Sheets(1).Range("R" & "1" & ":" & "R" & NoOfRows1).Interior.Color = RGB(255,255,0)

XLDoc.Sheets(1).Range("AA" & "1" & ":" & "AH" & 2).Interior.Color = RGB(255,255,0)

XLDoc.Sheets(1).Range("AH" & "1" & ":" & "AH" & NoOfRows1).Interior.Color = RGB(255,255,0)

'Orange'

XLDoc.Sheets(1).Range("C1:J2,S1:Z2").Interior.Color = RGB(255,192,0)

XLDoc.Sheets(1).Range("J" & "1" & ":" & "J" & NoOfRows1).Interior.Color = RGB(255,192,0)

XLDoc.Sheets(1).Range("Z" & "1" & ":" & "Z" & NoOfRows1).Interior.Color = RGB(255,192,0)

'Second Sheet - Italia Media Visite'

XLDoc.Sheets(2).Select

XLDoc.Sheets(2).Range("D1:J1").HorizontalAlignment = -4108

XLDoc.Sheets(2).Range("D1:J1").Merge

XLDoc.Sheets(2).Range("D1").Value = "Media Visite Ciclo1"

XLDoc.Sheets(2).Range("D1").Font.Bold = true

XLDoc.Sheets(2).Range("L1:R1").HorizontalAlignment = -4108

XLDoc.Sheets(2).Range("L1:R1").Merge

XLDoc.Sheets(2).Range("L1").Value = "Media Visite Ciclo2"

XLDoc.Sheets(2).Range("L1").Font.Bold = true

XLDoc.Sheets(2).Range("T1:Z1").HorizontalAlignment = -4108

XLDoc.Sheets(2).Range("T1:Z1").Merge

XLDoc.Sheets(2).Range("T1").Value = "Media Visite Ciclo3"

XLDoc.Sheets(2).Range("T1").Font.Bold = true

XLDoc.Sheets(2).Range("AB11:AH1").HorizontalAlignment = -4108

XLDoc.Sheets(2).Range("AB1:AH1").Merge

XLDoc.Sheets(2).Range("AB1").Value = "Media Visite Anuale"

XLDoc.Sheets(2).Range("AB1").Font.Bold = true

XLDoc.Sheets(2).Range("C3:AH35").NumberFormat = "0.00"

ActiveDocument.GetSheetObject(TableArray(1)).CopyTableToClipboard true

XLDoc.Sheets(2).Range("A2").Select

XLDoc.Sheets(2).PasteSpecial()

'find the number of records in the pasted table'

XLDoc.Sheets(2).Range("AI1").Formula = "=COUNT(R[1]C[-32]:R[498]C[-32])"

NoOfRows1 = XLDoc.Sheets(2).Range("AI1").Value + 2

XLDoc.Sheets(2).Range("AI1").Clear

'Section to delete if the client changes his mind'

XLDoc.Sheets(2).Range("C3:AH3").Clear

XLDoc.Sheets(2).Range("C3:AH3").Formula = "=Average(R[1]C:R[397]C)"

XLDoc.Sheets(2).Range("C3:AH3").NumberFormat = "0.00"

'End section'

'Lines for headers'

'Horizontal lines'

XLDoc.Sheets(2).Range("A3:AH3").Borders.LineStyle = xlContinuous

XLDoc.Sheets(2).Range("A3:AH3").Borders(9).Weight = 4

XLDoc.Sheets(2).Range("A3:AH3").Borders(8).Weight = 4

XLDoc.Sheets(2).Range("A3:AH3").Borders(9).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range("A3:AH3").Borders(8).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range("A" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders.LineStyle = xlContinuous

XLDoc.Sheets(2).Range("A" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(9).Weight = 4

XLDoc.Sheets(2).Range("A" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(9).Color = RGB(0,0,0)

'Vertical lines'

XLDoc.Sheets(2).Range( "B" & "1" & ":" & "B" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(2).Range( "B" & "1" & ":" & "B" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range( "C" & "1" & ":" & "C" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(2).Range( "C" & "1" & ":" & "C" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range( "J" & "1" & ":" & "J" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(2).Range( "J" & "1" & ":" & "J" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range( "K" & "1" & ":" & "K" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(2).Range( "K" & "1" & ":" & "K" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range( "R" & "1" & ":" & "R" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(2).Range( "R" & "1" & ":" & "R" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range( "S" & "1" & ":" & "S" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(2).Range( "S" & "1" & ":" & "S" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range( "Z" & "1" & ":" & "Z" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(2).Range( "Z" & "1" & ":" & "Z" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range( "AA" & "1" & ":" & "AA" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(2).Range( "AA" & "1" & ":" & "AA" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(2).Range( "AH" & "1" & ":" & "AH" & NoOfRows1 ).Borders(10).Weight = 4

XLDoc.Sheets(2).Range( "AH" & "1" & ":" & "AH" & NoOfRows1 ).Borders(10).Color = RGB(0,0,0)

'Yellow'

XLDoc.Sheets(2).Range("A" & "1" & ":" & "B" & NoOfRows1).Interior.Color = RGB(255,255,0)

XLDoc.Sheets(2).Range("K" & "1" & ":" & "R" & 2).Interior.Color = RGB(255,255,0)

XLDoc.Sheets(2).Range("R" & "1" & ":" & "R" & NoOfRows1).Interior.Color = RGB(255,255,0)

XLDoc.Sheets(2).Range("AA" & "1" & ":" & "AH" & 2).Interior.Color = RGB(255,255,0)

XLDoc.Sheets(2).Range("AH" & "1" & ":" & "AH" & NoOfRows1).Interior.Color = RGB(255,255,0)

'Orange'

XLDoc.Sheets(2).Range("C1:J2,S1:Z2").Interior.Color = RGB(255,192,0)

XLDoc.Sheets(2).Range("J" & "1" & ":" & "J" & NoOfRows1).Interior.Color = RGB(255,192,0)

XLDoc.Sheets(2).Range("Z" & "1" & ":" & "Z" & NoOfRows1).Interior.Color = RGB(255,192,0)

'For loop for pasting tables: Sheet NW to Isole'

dim i

i = 2

dim j

j = 2

while i < 7

XLDoc.Sheets(i+1).Select

XLDoc.Sheets(i+1).Range("A2").Select

ActiveDocument.GetSheetObject(TableArray(j)).CopyTableToClipboard true

XLDoc.Sheets(i+1).Paste()

XLDoc.Sheets(i+1).Range("AI1").Formula = "=COUNT(R[1]C[-32]:R[49]C[-32])"

NoOfRows1 = XLDoc.Sheets(i+1).Range("AI1").Value + 3

XLDoc.Sheets(i+1).Range("AI1").Clear

XLDoc.Sheets(i+1).Range("A" & NoOfRows1).Select

ActiveDocument.GetSheetObject(TableArray(j+1)).CopyTableToClipboard true

XLDoc.Sheets(i+1).Paste()

XLDoc.Sheets(i+1).Range("A" & NoOfRows1).EntireRow.Clear

j = j + 2

XLDoc.Sheets(i+1).Range("AI1").Formula = "=COUNT(R[1]C[-32]:R[38]C[-32])"

NoOfRows2 = XLDoc.Sheets(i+1).Range("AI1").Value + 1

XLDoc.Sheets(i+1).Range("AI1").Clear

'Headers fo tables'

XLDoc.Sheets(i+1).Range("C1:J1").HorizontalAlignment = -4108

XLDoc.Sheets(i+1).Range("C1:J1").Merge

XLDoc.Sheets(i+1).Range("C1").Font.Bold = true

XLDoc.Sheets(i+1).Range("C1").Value = "Visite Ciclo1"

XLDoc.Sheets(i+1).Range("K1:R1").HorizontalAlignment = -4108

XLDoc.Sheets(i+1).Range("K1:R1").Merge

XLDoc.Sheets(i+1).Range("K1").Font.Bold = true

XLDoc.Sheets(i+1).Range("K1").Value = "Visite Ciclo2"

XLDoc.Sheets(i+1).Range("S1:Z1").HorizontalAlignment = -4108

XLDoc.Sheets(i+1).Range("S1:Z1").Merge

XLDoc.Sheets(i+1).Range("S1").Font.Bold = true

XLDoc.Sheets(i+1).Range("S1").Value = "Visite Ciclo3"

XLDoc.Sheets(i+1).Range("AA1:AH1").HorizontalAlignment = -4108

XLDoc.Sheets(i+1).Range("AA1:AH1").Merge

XLDoc.Sheets(i+1).Range("AA1").Value = "Visite Anuale"

XLDoc.Sheets(i+1).Range("AA1").Font.Bold = true

'Header fo da odah tables'

XLDoc.Sheets(i+1).Range("C" & NoOfRows1 & ":" & "J" & NoOfRows1).HorizontalAlignment = -4108

XLDoc.Sheets(i+1).Range("C" & NoOfRows1 & ":" & "J" & NoOfRows1).Merge

XLDoc.Sheets(i+1).Range("C" & NoOfRows1).Value = "Media Visite Ciclo1"

XLDoc.Sheets(i+1).Range("C" & NoOfRows1).Font.Bold = true

  'Section to delete once the client changes his mind'

XLDoc.Sheets(i+1).Range("C3:AH3").Clear

XLDoc.Sheets(i+1).Range("C" & NoOfRows1 +1 & ":" & "R" & NoOfRows1 +1).Clear

'End section'

'Coloring time'

'Yellow mellow'

XLDoc.Sheets(i+1).Range("A1:B2").Interior.Color = RGB(255,255,0) 

'Orange Door hinge'

XLDoc.Sheets(i+1).Range("C1,S1").Interior.Color = RGB(255,192,0)

XLDoc.Sheets(i+1).Range("C2:J2,S2:Z2").Interior.Color = RGB(255,192,0)

XLDoc.Sheets(i+1).Range("C" & NoOfRows1).Interior.Color = RGB(255,192,0)

XLDoc.Sheets(i+1).Range("S" & NoOfRows1).Interior.Color = RGB(255,192,0)

'Draw lines - Horizontal'

XLDoc.Sheets(i+1).Range("A3:AH3").Borders.LineStyle = xlContinuous

XLDoc.Sheets(i+1).Range("A3:AH3").Borders(9).Weight = 4

XLDoc.Sheets(i+1).Range("A3:AH3").Borders(7).Weight = 4

XLDoc.Sheets(i+1).Range("A3:AH3").Borders(10).Weight = 4

XLDoc.Sheets(i+1).Range("A3:AH3").Borders(8).Weight = 4

XLDoc.Sheets(i+1).Range("A3:AH3").Borders(8).Color = RGB(0,0,0)

XLDoc.Sheets(i+1).Range("A3:AH3").Borders(9).Color = RGB(0,0,0)

XLDoc.Sheets(i+1).Range("A" & NoOfRows2 & ":" & "AH" & NoOfRows2 + 2).Borders(9).Color = RGB(0,0,0)

XLDoc.Sheets(i+1).Range("A" & NoOfRows2 & ":" & "AH" & NoOfRows2 + 2).Borders(9).Weight = 4

'<Media Visite Ciclo> lines'

XLDoc.Sheets(i+1).Range( "C" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders.LineStyle = xlContinuous

XLDoc.Sheets(i+1).Range( "C" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(9).Weight = 4

XLDoc.Sheets(i+1).Range( "C" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(7).Weight = 4

XLDoc.Sheets(i+1).Range( "C" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(10).Weight = 4

XLDoc.Sheets(i+1).Range( "C" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(8).Weight = 4

XLDoc.Sheets(i+1).Range( "C" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(8).Color = RGB(0,0,0)

XLDoc.Sheets(i+1).Range( "C" & NoOfRows1 & ":" & "AH" & NoOfRows1).Borders(9).Color = RGB(0,0,0)

'Line after <Media Visite Ciclo>'

XLDoc.Sheets(i+1).Range( "A" & NoOfRows1 + 1 & ":" & "AH" & NoOfRows1 + 1).Borders.LineStyle = xlContinuous

XLDoc.Sheets(i+1).Range( "A" & NoOfRows1 + 1 & ":" & "AH" & NoOfRows1 + 1).Borders(9).Weight = 4

XLDoc.Sheets(i+1).Range( "A" & NoOfRows1 + 1 & ":" & "AH" & NoOfRows1 + 1).Borders(7).Weight = 4

XLDoc.Sheets(i+1).Range( "A" & NoOfRows1 + 1 & ":" & "AH" & NoOfRows1 + 1).Borders(10).Weight = 4

XLDoc.Sheets(i+1).Range( "A" & NoOfRows1 + 1 & ":" & "AH" & NoOfRows1 + 1).Borders(8).Weight = 4

XLDoc.Sheets(i+1).Range( "A" & NoOfRows1 + 1 & ":" & "AH" & NoOfRows1 + 1).Borders(8).Color = RGB(0,0,0)

XLDoc.Sheets(i+1).Range( "A" & NoOfRows1 + 1 & ":" & "AH" & NoOfRows1 + 1).Borders(9).Color = RGB(0,0,0)

'Vertical Lines'

XLDoc.Sheets(i+1).Range( "B" & "1" & ":" & "B" & NoOfRows2 + 2).Borders(10).Weight = 4

XLDoc.Sheets(i+1).Range( "B" & "1" & ":" & "B" & NoOfRows2 + 2).Borders(10).Color = RGB(0,0,0)

XLDoc.Sheets(i+1).Range( "J" & "1" & ":" & "J" & NoOfRows2 + 2).Borders(10).Weight = 4

XLDoc.Sheets(i+1).Range( "J" & "1" & ":" & "J" & NoOfRows2 + 2).Borders(10).Color = RGB(0,0,0)

'Final columns set up'

XLDoc.Sheets(i+1).Columns("B:B").ColumnWidth = 11.2

i = i + 1

wend

XLDoc.Sheets(3).Range("C3:AH3").Formula = "=AVERAGE(R[1]C:R[9]C)"

XLDoc.Sheets(3).Range("C14:AH14").Formula = "=AVERAGE(R[1]C:R[9]C)"

XLDoc.Sheets(3).Range("C3:AH3,C14:AH14").NumberFormat = "0.00"

XLDoc.Sheets(4).Range("C3:AH3").Formula = "=AVERAGE(R[1]C:R[6]C)"

XLDoc.Sheets(4).Range("C11:AH11").Formula = "=AVERAGE(R[1]C:R[6]C)"

XLDoc.Sheets(4).Range("C3:AH3,C11:AH11").NumberFormat = "0.00"

XLDoc.Sheets(5).Range("C3:AH3").Formula = "=AVERAGE(R[1]C:R[5]C)"

XLDoc.Sheets(5).Range("C10:AH10").Formula = "=AVERAGE(R[1]C:R[5]C)"

XLDoc.Sheets(5).Range("C3:AH3,C12:AH12").NumberFormat = "0.00"

XLDoc.Sheets(6).Range("C3:AH3").Formula = "=AVERAGE(R[1]C:R[7]C)"

XLDoc.Sheets(6).Range("C12:AH12").Formula = "=AVERAGE(R[1]C:R[7]C)"

XLDoc.Sheets(6).Range("C3:AH3,C12:AH12").NumberFormat = "0.00"

XLDoc.Sheets(7).Range("C3:AH3").Formula = "=AVERAGE(R[1]C:R[5]C)"

XLDoc.Sheets(7).Range("C10:AH10").Formula = "=AVERAGE(R[1]C:R[5]C)"

XLDoc.Sheets(7).Range("C3:AH3,C10:AH10").NumberFormat = "0.00"

XLApp.Visible = True

460 Views
0 Likes
0 Replies