Insert Text array as Headers (VBA, EXCEL)
<p><strong>BACKGROUND</strong></p>

<p>I've created a VBA macro to pull in financial data from various business units.</p>

<p><strong>OBJECTIVE</strong></p>

<p>After consolidating each business unit's financial information, <strong>insert a row with column headers</strong> to prepare for a pivot table report.</p>

<p><strong>APPROACH</strong></p>

<ol>
<li>Define worksheet</li>
<li>Define "header" array</li>
<li>Insert 1 row at top of excel sheet, shift all other rows down by 1</li>
<li>Insert header array text values into columns</li>
</ol>

<p><strong>CODE BASE</strong></p>

<pre><code>Sub addHeaders()

Dim ws As Worksheet
Dim headers() As Variant

'Define worksheet and desired headers
Set ws = ThisWorkbook.Sheets("CONSOLIDATED")
headers() = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")


'Insert row for header placement
Rows(1).Insert shift:=xlShiftDown


'Insert headers
With ws
For i = LBound(headers()) To UBound(headers())
    .Cells(1, 1 + i).Value = headers(i)
    Next i
End With


End Sub
</code></pre>

<p><strong>ISSUE(S)</strong></p>

<ol>
<li>"Object variable or With block variable are not set" @ line <code>.Cells(1, 1 + i).Value = headers(i)</code></li>
</ol>

<p><strong>QUESTION</strong></p>

<ul>
<li>Why is VBA saying that the variable (assuming "ws") was not set when I've clearly defined <code>Set ws = ThisWorkbook.Sheets("CONSOLIDATED")</code></li>
</ul>