Krdytkyu

As an old dog (age 73) learning new (Excel VBA) tricks, I am reasonably happy with putting together the code below. But I think it could be cleaner. How would you have coded it?

Private Sub Workbook_Open()  

    Dim lastRow As Long     'last row with data  

    Dim thisDate As Double  'start timestamp  

    thisDate = Now()  

With Sheets("Pressure Log")  

    lastRow = .Range("B" & .Rows.Count).End(xlUp).Row 'populate next row with date/time  

    .Range("B" & lastRow + 1 & ":G" & lastRow + 1).Borders.LineStyle = xlContinuous  

    .Range("B" & lastRow).Offset(1) = Format(thisDate, "dddd")  

    .Range("B" & lastRow).Offset(1, 1) = Format(thisDate, "mm/dd/yyyy")  

    .Range("B" & lastRow).Offset(1, 2) = Format(thisDate, "hh:mm AM/PM")  

    .Range("B" & lastRow).Offset(1, 3).Select 'position for data  

End With  

End Sub

Properly formatting and indenting code is always a good start.

Using Option Explicit at the top of every module is a must. You may already do this, just thought I would mention it.

You declare thisDate as a Double but you use it as a Date. Declare it as a Date.

Make your life a little easier and set a range to the start of your new row instead of calling a calculated range. Example below:

Private Sub Workbook_Open()  

    Dim lastRow As Long     'last row with data  

    Dim thisDate As Date  'start timestamp  

    Dim entryRange as Range

    thisDate = Now()  

    With Sheets("Pressure Log")  

        lastRow = .Range("B" & .Rows.Count).End(xlUp).Row 'populate next row with date/time  

        Set entryRange = .Range("B" & lastRow+1) ` There are other ways of doing this too.   

    End With  

    entryRange.resize(1, 6).Borders.LineStyle = xlContinuous  ' Yes, could do this in a With block as well.

    entryRange.Value = Format(thisDate, "dddd")  

    entryRange.Offset(, 1).Value = Format(thisDate, "mm/dd/yyyy")  

    entryRange.Offset(, 2) = Format(thisDate, "hh:mm AM/PM")  

    entryRange.Offset(, 3).Select 'position for data  

End Sub

Another With statement would make the code easier to read.

With Sheets("Pressure Log")

    lastRow = .Range("B" & .Rows.Count).End(xlUp).Row    'populate next row with date/time

    .Range("B" & lastRow + 1 & ":G" & lastRow + 1).Borders.LineStyle = xlContinuous

    With .Range("B" & lastRow)

        .Offset(1) = Format(thisDate, "dddd")

        .Offset(1, 1) = Format(thisDate, "mm/dd/yyyy")

        .Offset(1, 2) = Format(thisDate, "hh:mm AM/PM")

        .Offset(1, 3).Select                      'position for data

    End With

End With

It is better to format entire columns and rows. These reduces the file size by combining multiple css rules.

There are many way to skin this cat. For variety I set a range variable ( newRow ) to the next empty row. This allows me to uses ranges relative to the new row.

Dim newRow As Range

With Worksheets("Pressure Log")

    Set newRow = .Range("B" & .Rows.Count).End(xlUp).Offset(1).EntireRow

    newRow.Range("B1:G1").Borders.LineStyle = xlContinuous

    newRow.Range("B1:D1").Value = Now

    .Columns("C").NumberFormat = "ddd"

    .Columns("D").NumberFormat = "mm/dd/yyyy"

    .Columns("E").NumberFormat = "hh:mm AM/PM"

    .Columns("C:G").AutoFit

    newRow.Columns("E").Select

End With

With this setup we can copy all the formats from the row above except NumberFormats by using Range().FillDown.

 newRow.FillDown

Alternately, we could copy all the formats from the row above like this:

newRow.Offset(-1).Copy

newRow.PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

Your code is pretty solid but if you truly want to learn Excel VBA I would start by watching this series: Excel VBA Introduction.

You should also use Rubberduck VBA. It is a free add-in with many features to help you write better code. The code formatter alone is invaluable.

Last but not least start answering questions on StackOverflow. This will give you exposure to questions that you would never think to ask and solutions that you didn't know where possible.