Looping over multiple worksheets and finding the last blank cell and putting the max date header & formula from the range L:AD

Question

I am very new to VBA and trying to automate one of my repetitive tasks. I have multiple sheets in a workbook and the range from L upto a dynamic number of columns ( I say dynamic number of columns because for some sheets I have 2 columns and for some I have 14 columns) I have various dates. I need to go to the last blank cell, give the column header as "max date" and find out the max date from the range of dates

I have tried this script myself but getting errors

Worksheets(1).Activate
Dim singlesheet As Worksheet
Application.ScreenUpdating = False

For Each singlesheet In Worksheets
    singlesheet(Range("A1", Range("A1").End(xlDown))).Rows.Count
    Range("A1").Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = "Max date"
Next singlesheet

I get a Run time error 438

Object does not support this property or method

Answer 1

In order to write down "Max Date" in the header row, at the right column of every sheet, you can use something like the code below.

As mentioned in SO many times, you should stay away from using Activate , ActiveCell and Select.

Modified Code

Option Explicit

Sub MaxDateSheets()

Dim SingleSheet As Worksheet
Dim LastCol As Long
Dim LastCell As Range

Application.ScreenUpdating = False

For Each SingleSheet In ThisWorkbook.Worksheets
    With SingleSheet
        ' Use Find function to get the last column in sheet
        Set LastCell = .Cells.Find(What:="*", after:=.Cells(1), LookAt:=xlPart, LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
        If Not LastCell Is Nothing Then
            LastCol = LastCell.Column
        Else
            MsgBox "Error! worksheet is empty", vbCritical
            Exit Sub
        End If

        .Cells(1, LastCol).Value = "Max date" ' write "Max Date" in the header of the right column
    End With
Next SingleSheet

Application.ScreenUpdating = True

End Sub

Answer 2

Try this code:

Sub fnd_max()

Dim singlesheet As Worksheet

For Each singlesheet In Worksheets

    lr = LastRow(singlesheet)
    lc = LastCol(singlesheet)

    With singlesheet
    If lc > 0 And lr > 0 Then
        singlesheet.Activate
        .Cells(1, lc + 1).Value = "Max Header"
        .Cells(2, lc + 1).formula = "=MAX(" & .Range(Cells(2, 12), Cells(2, lc)).Address(0, 0) & ")"
        .Cells(2, lc + 1).AutoFill Destination:=.Range(Cells(2, lc + 1), Cells(lr, lc + 1))
    End If
    End With

Next

End Sub

Function LastRow(Sh As Worksheet)
    On Error Resume Next
    LastRow = Sh.Cells.Find(What:="*", _
                            After:=Sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
    On Error GoTo 0
End Function

Function LastCol(Sh As Worksheet)
    On Error Resume Next
    LastCol = Sh.Cells.Find(What:="*", _
                            After:=Sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function