Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

0 Replies