# 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.
###### Who is Participating?

Commented:
Hi,

Try

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, 2).Column
.Cells(1, c + 1).Value = "Month_Year"
.Cells(2, c + 1).Resize(r - 1).Formula = "=date(h2,i2,1)"
End With
Next

End Sub
``````
0

Commented:
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
``````
0

Commented:
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
``````
0

Commented:
Hi,

replace

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

with

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

Kris
0

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

Author Commented: