insert a column if not pre-existing and save the file(while consolidation)

[aprvvsh]

While consolidating multiple sheets I have to insert a column along with the header value if it doesn't exist already and save the file too.
I tried following but its not changing the column value.

If Workbooks(filename).Sheets("ABC").Cells("F1").Value <> "Header" Then
  Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Workbooks(filename).Sheets("ABC").Cells("F1").Value = "Header"
  End If

 

[A J]

Hi aprvvsh,

Try to change the cell reference used in code to below-

If Workbooks(Filename).Sheets("ABC").Cells(1, 6).Value <> "Header" Then

Above worked fine for me.

Cheers
AJ

 

[aprvvsh]

Thanks for quick reply but the file is to be saved with the made changes;how to do that?

 

[A J]

Hi aprvvsh,

See code below


If Workbooks(Filename).Worksheets("sheet1").Cells(1, 6).Value <> "Header" Then
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Workbooks(Filename).Sheets("sheet1").Cells(1, 6).Value = "Header"
Workbooks(Filename).Save
'''OR add below to save and close file
''' Workbooks(Filename).Close SaveChanges:=True
end if


Cheers