PDA

View Full Version : Automatically Inserting Rows with Date, Each Worksheet



jaspr
03-31-2014, 06:34 AM
Hi folks. Some of you have already helped me with deleting rows upon opening a worksheet. Now I am asking for help again with inserting rows -- I promise this is the last time.

Can someone help me with the VBA code to insert a row in the last row of my table, with today's date in Column B? Here's the catch - my workbook has multiple worksheets (tabs), each containing a single table. And, I need this action performed for every worksheet in the workbook, when the workbook is opened. On top of it all, I don't want the action repeated if the workbook is opened multiple times in the same day - only the first time it is opened.

Thanks again in advance. You folks are great. I'm not sure why you choose to help total strangers with their tech problems, but I think it's fantastic.

jaspr

Bob Phillips
03-31-2014, 10:18 AM
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim lastrow As Long

For Each ws In ThisWorkbook.Worksheets

wsDeleteDateRows ws, 2, 6

lastrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
If Application.CountIf(ws.Range("B6").Resize(lastrow - 5), Date) = 0 Then

ws.Cells(lastrow + 1, "B").Value = Date
ws.Cells(lastrow, "B").Copy
ws.Cells(lastrow + 1, "B").PasteSpecial Paste:=xlPasteFormats
End If
Next ws
End Sub