Filter table based on month selected?

[gino59]

Hi all,

I have the following bit of code that looks up a date range in a very large table and returns the data from that to a new table on a different sheet. The way I've got the code written, I have to manually change the date selection. I'm hoping some one knows a way around that!

Basically, I want to go to the master table and filter for a particular month (say April 2010). Then write the data for that month to the new table.

It works with this code but I'd like the user to be able to select a date (maybe from a calendar) and have the code return the data.

Hope that makes sense!

Private Sub Macro1()
'
    Sheets("mData").Activate
    ActiveSheet.ListObjects("mData").Range.AutoFilter field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "3/31/2011")
    Range("mData[DATE],mData[[ACCOUNT]:[DEPOSIT]]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Activate
    Range("ThisMONTHtx[[DATE]:[DEPOSIT]]").Select
    ActiveSheet.Paste
    Sheets("mData").Activate
    ActiveSheet.ListObjects("mData").Range.AutoFilter field:=1
    Sheets("Sheet1").Activate
    Range("C20").Activate
End Sub

 

[gino59]

Got it working in case anyone wants to know...

Sub Get_ThisMONTH()
'
'    This procedure gets the current month's transactions from the mData table and
'    writes them to the Sheet1 worksheet.  The formulas on this worksheet reference
'    this new table (ThisMONTHtx).
'
Dim MOsel As String
Dim osh As Worksheet
Set osh = ActiveSheet
MOsel = ActiveSheet.Range("B25")
'
'    Clear out the old table data.
     Application.ScreenUpdating = False
     Sheets("Sheet1").Activate
          With osh.ListObjects("ThisMONTHtx")
               .DataBodyRange.Select
               Selection.Delete
          End With
'
'    Filter and copy this month's transactions from the mData table.
     Sheets("mData").Activate
     ActiveSheet.ListObjects("mData").Range.AutoFilter field:=1, Operator:= _
          xlFilterValues, Criteria2:=Array(1, MOsel)
     Range("mData[DATE],mData[[ACCOUNT]:[DEPOSIT]]").Select
     Application.CutCopyMode = False
     Selection.Copy
'
'    Paste this month's transactions to the ThisMONTHtx table.
     Sheets("Sheet1").Activate
     Range("ThisMONTHtx[[DATE]:[DEPOSIT]]").Select
     ActiveSheet.Paste
'
'    Format the date column in the table and clear the mData table filter.
     Range("ThisMONTHtx[DATE]").Select
          Selection.NumberFormat = "m/d/yyyy"
     Sheets("mData").Activate
          ActiveSheet.ListObjects("mData").Range.AutoFilter field:=1
     Sheets("Sheet1").Activate
          ActiveWorkbook.RefreshAll
          Range("M6").Activate
     Application.ScreenUpdating = True
End Sub