Pulling todays records from a table

[Mr_Phil]

My brain is telling me that this ought to be simple. But..... it aint working.

table1 has three columns DATE, TRANS CODE, NOTE.

table 1 has a few thousand rows. On any given day we do anywhere from one to a hundred transactions. So, as a courtesy I'd like to quickly pull todays transactions and send only those rows in an email. I was thinking that if I could extract todays transactions and have them appear on a separate tab I could just copy/paste into outlook. But I cannot find an easy way to do it. If it was msaccess I would write a query and be done with it. But, alas, we got to work with what we have.

What have I overlooked?

[JohnTopley]

Why not filter on Today's date, copy and paste to new tab?

[Mr_Phil]

Wow that was fast :-)

I'm doing exactly that right now. I have another guy that inputs into the main data page who isn't spreadsheet friendly. The separate tab idea was mainly to allow him to do what I am doing without any further complications. He's a great guy. But his excel skills are limited to typing, hit tab to go to the next cell, type, hit enter.

[alansidman]

Here is some untested VBA code that should help

Option Explicit

Sub TodayTrans()
    Dim lr As Long, i As Long
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A2:C" & lr2).ClearContents
    With s1
        For i = 2 To lr
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
            If .Range("A" & i).Value = Date Then
                .Range("A" & i & ":C" & i).Copy s2.Range("A" & lr2 + 1)
            End If
        Next i
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox ("Ready to email")
End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

EDIT: I think that John's solution will run faster.

[JohnTopley]

By tab I mean sheet! Confused by your response!!

Select all Today's transaction in Sheet1, copy and paste to Sheet2.