asked on

Create calculated column on multiple worksheets

I need to add a calculated column to the right of the last column in several populated worksheets (in this case it is sheets 1 thru 5 from the same workbook).
I would like to create a column named Month_Year based on this formula DATE(H2|I2|1)
That creates the same number of rows as in the column to the immediate left of the calculated column. Worksheets 1-5 contain different numbers of columns and rows of data.

ragnarok89

This should work.

Sub FindLastColumn()
'Finds last used column, copies and selects the next
Dim LastColumn As Integer
'Find last column with text
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Columns.
        LastColumn = Cells.Find(What:="*", After:=[A1], _
                           SearchOrder:=xlByColumns, _
                       SearchDirection:=xlPrevious).Column
    End If
    
'se;ect first row in last column
cells(1,lastcolumn).select

-- do other stuff ---

End Sub

Open in new window

krishnakrkc

Hi,

may be...

Kris

Sub kTest()
    Dim i As Long, r   As Long, c As Long, MySheets
    
    MySheets = Array("Sheet1", "Sheet2", "Sheet3") '<<=== add more sheets
    
    For i = 0 To UBound(MySheets)
        With Worksheets(CStr(MySheets(i)))
            r = .Range("h" & .Rows.Count).End(xlUp).Row
            c = .UsedRange.Find("*", .[a1], , , , 2).Column
            .Cells(2, c + 1).Resize(r - 1).Formula = "=date(h2,i2,1)"
        End With
    Next
    
End Sub

Open in new window

krishnakrkc

Hi,

replace

c = .UsedRange.Find("*", .[a1], , , , 2).Column

with

c = .UsedRange.Find("*", .[a1], , , 2, 2).Column

Kris

Ed_CLP

ASKER

@krishnakrkc - that is perfect! Can the column title(Month_year) be set as part of the script?

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ed_CLP

ASKER

Completely answered my question.