- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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