Add new column in excel using Visual Basic

[matrik02]

I have 10 excel files in the folder. Each of the files have five columns. How I can add another one new column name "CATATAN" at the end of the columns so that I have six column.

[seenu_1st]

excel already has more columns, do u mean to hide / unhide column?

[matrik02]

Actually, I have table with 5 columns "A", "B", "C", "D","E". I would like to insert new column in the table using visual basic. The new columns in the table is "CATATAN". It take time to do manually one by one.

[jcis]

Try this (sh is the WorkSheet Object)

    With sh.UsedRange
        sh.Cells(.Row, .Column + .Columns.Count) = "CATATAN"
    End With

A complete example loading a WorkBook, making this change, saving and exiting:

Dim ex As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet

    Set ex = New Excel.Application
    Set wb = ex.Workbooks.Open("C:\wb1.xls")
    Set sh = wb.Worksheets(1)
        
    With sh.UsedRange
        sh.Cells(.Row, .Column + .Columns.Count) = "CATATAN"
    End With
    
    wb.Save
    Set sh = Nothing
    wb.Close
    Set wb = Nothing
    ex.Application.Quit
    Set ex = Nothing

[matrik02]

It added in the first row. How to added it on the fourth rows within the tables

[jcis]

Dim ex As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet, i As Long

    Set ex = New Excel.Application
    Set wb = ex.Workbooks.Open("C:\wb1.xls")
    Set sh = wb.Worksheets(1)
        
    With sh.UsedRange
        For i = .Row To .Row + .Rows.Count - 1
            sh.Cells(i, .Column + .Columns.Count) = "CATATAN"
        Next
    End With
    
    wb.Save
    Set sh = Nothing
    wb.Close
    Set wb = Nothing
    ex.Application.Quit
    Set ex = Nothing