This Excel VBA Code, shown below was previously posted in 2016 and it worked, but now it doesn't. The column headers are all renamed and then I get this error: "Object Variable or With block variable not set". Debug highlights the row which is shown in BOLD. I'm not sure where to look?
Sub ChangeColumnHeaders()
'ONE - This sub renames all of the column headers from NSS that are applicable to the access import AND
'Inserts all additional columns and names the column headers
Dim ColHeads
Dim i As Long
Sheets("Shelter").Select
ColHeads = Worksheets("Column_Headers").Cells(1).CurrentRegion.Value
With Worksheets("Shelter")
For i = LBound(ColHeads, 1) To UBound(ColHeads, 1)
.Rows(1).Find(ColHeads(i, 1)).Value = ColHeads(i, 2)
Next i
End With
'Insert Column to the left of Column D 'SSecondaryPhoneType to the right of SSecondary Phone
Columns("E:E").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Worksheets("Shelter").Range("E1").Value = "SSecondaryPhoneType"
'Insert Column to the left of Column Z 'CPrimaryPhoneType to the right of CPrimaryPhoneExt
Columns("Z:Z").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Worksheets("Shelter").Range("Z1").Value = "CPrimaryPhoneType"
'Insert 2 Columns to the left of Column AB1 & AC1 ''CAlternatePhoneType to the right of CAlernatePhone
Columns("AB:AC").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Worksheets("Shelter").Range("AB1").Value = "CAlternatePhoneType"
Worksheets("Shelter").Range("AC1").Value = "CAlternatePhoneExt"
'Insert 1 Columns to the left of Column AJ1 '
Columns("AJ:AJ").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Worksheets("Shelter").Range("AJ1").Value = "24HrPOCPhoneType"
'Insert 1 Columns to the left of Column AQ1
Columns("AQ:AQ").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Worksheets("Shelter").Range("AQ1").Value = "AlternateContact1PhoneType"
'Insert 1 Columns to the left of Column AX1
Columns("AX:AX").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Worksheets("Shelter").Range("AX1").Value = "AlternateContact2PhoneType"
Call RemovePhoneDashes
End Sub 'Change_and_Rename_Headers_