Insert Text array as Headers (VBA, EXCEL)

Question

BACKGROUND

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

OBJECTIVE

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

APPROACH

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

CODE BASE

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

ISSUE(S)

"Object variable or With block variable are not set" @ line .Cells(1, 1 + i).Value = headers(i)

QUESTION

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

Are you sure the sheet is in ThisWorkbook and not ActiveWorkbook ? — SierraOscar
– SierraOscar, Commented Sep 26, 2016 at 14:11
EDIT: For some reason, when I am outside the VBA editor, this code runs appropriately. All code above works. Apologies for an inconvenience — jonplaca
– jonplaca, Commented Sep 26, 2016 at 14:12

SierraOscar · Accepted Answer · 2016-09-26 14:15:35Z

2

I suspect that maybe the worksheet isn't where you're telling the code it is, or that the sheet name is in fact incorrect. (Possibly confusing ThisWorkbook with ActiveWorkbook?)

You could also re-write the code like so:

Sub InsertHeaders()

    Sheets("CONSOLIDATED").Cells(1).Resize(1, 6).Value = Array("Fiscal Year", "Month", "Month_Year", "Project", "Local Expense", "Base Expense")

End Sub

answered Sep 26, 2016 at 14:15

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert Text array as Headers (VBA, EXCEL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related