VBA to drag a row with formula but for current month only

Question

I have a sheet that shows monthly data. It includes columns for the date and a countif formula.

What I want to do: When the code runs on every first day of the month, VBA code will automatically hide the previous month (e.g. July) and creates new rows for current month (e.g. August). These rows for current month should also have the dynamic countif formula.

What I'm trying to do:

I am trying to get the last row address of the previous month(July), then add an offset to start the series for the current month(August).

Set laslastday = Range("K1").Value ' Last day of previous month
Set sht = ActiveSheet.UsedRange.Columns("A").SpecialCells(xlCellTypeVisible)

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

   For Each cl In sht.Cells
    With cl
        If (.Value = laslastday) Then
            .Offset(1, 0).Activate
              '-- dont know what's next             

        End If
    End With
Next

Here's a sample image...

It should start the copying of rows on the Yellow cell but only until August 31. Then hide the July rows.

It should also do the same as to when this will be generated. (e.g. September, October, etc.)

Mikku Mikku · Accepted Answer · 2019-08-04T03:38:42

Notes:

Will pick the lastday from LastRow of Column k
Will put the Dates for a Month
Will hide Rows from 3:LastRow

Code:

With ActiveSheet

LastRow = .Cells(ActiveSheet.Rows.Count, "B").End(xlUp).row
lastday = Range("K" & LastRow).Value
dd = Day(DateSerial(Year(lastday + 1), Month(lastday + 1) + 1, 0))

    For i = 1 To dd

        .Cells(LastRow + i, "K").Value = lastday + i
        .Cells(LastRow + i, "K").NumberFormat = "mm/dd/yyyy"
    Next

.Range("L" & LastRow & ":N" & LastRow).Copy
.Range("L" & LastRow + 1 & ":N" & LastRow + dd).PasteSpecial xlPasteFormulas

.Rows(3 & ":" & LastRow).EntireRow.Hidden = True

End With

VBA to drag a row with formula but for current month only

1 Answers