insert a column after last column with data

[vijaycs]

I want to insert a new column (with the name) after last column with data.

i tried using the below code by writing it in VBA module, however it does not work.


[
Sub add_column()


Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
Set ws = Sheet1 ' NOTE: Change this if your data is not in Sheet1.

With ws
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Cells(1, LastCol + 1).Value = "XXXX"
.Cells(1, LastCol + 2).Value = "YYYY"
End With
End Sub
]

 

[mumps]

It works for me. How is it not working for you?

 

[Fluff]

Hi & welcome to MrExcel.
In what way doesn't it work?

 

[DanteAmor]

I want to insert a new column (with the name) after last column with data.

i tried using the below code by writing it in VBA module, however it does not work.

Sub add_column()
Dim LastRow As Long
    Dim LastCol As Long
    Dim iRow As Long
    Set ws = Sheet1   ' NOTE: Change this if your data is not in Sheet1.
    
    With ws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastCol = .Cells([SIZE=3][B][COLOR=#ff0000]1[/COLOR][/B][/SIZE], .Columns.Count).End(xlToLeft).Column
        
        .Cells([B][COLOR=#FF0000]1[/COLOR][/B], LastCol + 1).Value = "XXXX"
        .Cells([B][COLOR=#FF0000]1[/COLOR][/B], LastCol + 2).Value = "YYYY"
    End With
End Sub

To make it work, you must put the row where you have data, if your headings are in row 2, then change the 1 to the 2.

 

[Mentor82]

Hi,
How about this?

Sub add_column()


Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
Set ws = Sheets(1) ' NOTE: Change this if your data is not in Sheet1.

With ws
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Columns(LastCol+1).EntireColumn.Insert
.Cells(1,LastCol+1).Value = "XXXX"
.Columns(LastCol+2).EntireColumn.Insert
.Cells(1, LastCol + 2).Value = "YYYY"
End With
End Sub

 

[vijaycs]

It works for me. How is it not working for you?

Hi,

yes it works for me too,.
however when the module is saved in that particular excel workbook it work but not in personal . thanks

 

[Fluff]

In that case how about

Sub add_column()


Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
[COLOR=#0000ff]Set ws = ActiveSheet[/COLOR]

With ws
   LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
   LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
   
   .Cells(1, LastCol + 1).Value = "XXXX"
   .Cells(1, LastCol + 2).Value = "YYYY"
End With
End Sub

This will work on whatever sheet is active

 

[vijaycs]

Thank you Mentor82. it worked perfectly.