Hello Everyone.
I just recently got upgraded from Excel 2013 to Excel 2016. When I had 2013 version, I built a vba code where I created an Excel workbook instance, posted some data into it via Access vba and then once it is done the code will connect to my Personal.xlsb and run a build in macro, to format the data that was just posted into a new workbook. The issue is that once I transferred my vba codes into the new Access and Excel, I started to get an error stating that it it error code 9. Out of range.
I played around with the code, and was able to identify that the issue is occurring when I run Excel macro. The process cant Identify which workbook to use. Because of this, I added a new code highlighted in green. It works fin, but the issue is that a newly created workbook would have to have a static name- Book4, but it is not always the case. When I run this process multiple times during the day, the process creates a new workbook with a new name (book1, book2). I tried various options to make make Windows active with a variable name, but it was not successful.
Can someone please help me to figure this out?
Here is my code:
I just recently got upgraded from Excel 2013 to Excel 2016. When I had 2013 version, I built a vba code where I created an Excel workbook instance, posted some data into it via Access vba and then once it is done the code will connect to my Personal.xlsb and run a build in macro, to format the data that was just posted into a new workbook. The issue is that once I transferred my vba codes into the new Access and Excel, I started to get an error stating that it it error code 9. Out of range.
I played around with the code, and was able to identify that the issue is occurring when I run Excel macro. The process cant Identify which workbook to use. Because of this, I added a new code highlighted in green. It works fin, but the issue is that a newly created workbook would have to have a static name- Book4, but it is not always the case. When I run this process multiple times during the day, the process creates a new workbook with a new name (book1, book2). I tried various options to make make Windows active with a variable name, but it was not successful.
Can someone please help me to figure this out?
Here is my code:
Code:
Sub FinalReport()
Dim ws As Worksheet
[COLOR=#008000]Windows("Book4").Activate[/COLOR]
Set ws = Application.z.Sheets("MonthlyReport")
'Set ws = Application.ActiveWorkbook.Sheets("MonthlyPReport")
ws.Columns("A:Q").AutoFit
ws.Range("A1").Font.Bold = True
ws.Range("A1").Font.Size = 11
ws.Range("A1").HorizontalAlignment = xlCenter
ws.Range("B1").Font.Bold = True
ws.Range("B1").Font.Size = 11
ws.Range("B1").HorizontalAlignment = xlCenter
ws.Range("C1").Font.Bold = True
ws.Range("C1").Font.Size = 11
ws.Range("C1").HorizontalAlignment = xlCenter
ws.Range("D1").Font.Bold = True
ws.Range("D1").Font.Size = 11
ws.Range("D1").HorizontalAlignment = xlCenter
ws.Range("E1").Font.Bold = True
ws.Range("E1").Font.Size = 11
ws.Range("E1").HorizontalAlignment = xlCenter
ws.Range("F1").Font.Bold = True
ws.Range("F1").Font.Size = 11
ws.Range("F1").HorizontalAlignment = xlCenter
ws.Range("G1").Font.Bold = True
ws.Range("G1").Font.Size = 11
ws.Range("G1").HorizontalAlignment = xlCenter
ws.Range("H1").Font.Bold = True
ws.Range("H1").Font.Size = 11
ws.Range("H1").HorizontalAlignment = xlCenter
ws.Range("I1").Font.Bold = True
ws.Range("I1").Font.Size = 11
ws.Range("I1").HorizontalAlignment = xlCenter
ws.Range("J1").Font.Bold = True
ws.Range("J1").Font.Size = 11
ws.Range("J1").HorizontalAlignment = xlCenter
ws.Range("K1").Font.Bold = True
ws.Range("K1").Font.Size = 11
ws.Range("K1").HorizontalAlignment = xlCenter
ws.Range("L1").Font.Bold = True
ws.Range("L1").Font.Size = 11
ws.Range("L1").HorizontalAlignment = xlCenter
ws.Range("M1").Font.Bold = True
ws.Range("M1").Font.Size = 11
ws.Range("M1").HorizontalAlignment = xlCenter
ws.Range("N1").Font.Bold = True
ws.Range("N1").Font.Size = 11
ws.Range("N1").HorizontalAlignment = xlCenter
ws.Range("O1").Font.Bold = True
ws.Range("O1").Font.Size = 11
ws.Range("O1").HorizontalAlignment = xlCenter
ws.Range("P1").Font.Bold = True
ws.Range("P1").Font.Size = 11
ws.Range("P1").HorizontalAlignment = xlCenter
ws.Range("Q1").Font.Bold = True
ws.Range("Q1").Font.Size = 11
ws.Range("Q1").HorizontalAlignment = xlCenter
Range("A1:Q1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColor = 12632256
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With ws.Range("A1:Q1").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With ws.Range("A1:Q1").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With ws.Range("A1:Q1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With ws.Range("A1:Q1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With ws.Range("A1:Q1").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
End Sub
Last edited by a moderator: