Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Filter by Date Range Copy and Paste to Sheet

  1. #1
    Join Date
    1st November 2011
    Posts
    4

    Filter by Date Range Copy and Paste to Sheet

    I would like to create a macro or VBA script that will work from a button to Filter records from sheet 2 based on a date range. Then copy the visible records and go to another sheet clear that sheet and paste the copied records. I have recorded this Macro to do it by choosing the dates under "Date Filter" "in Between" but I would like them to be able to enter the dates in to two cells and the filter use those values as the date range to filter with. I am triggering this from a button and entry cells on Sheet 1 "Entry" that triggers the filter of sheet 2 "QuickList_test" (based on date range) then pastes it to sheet 4 "Renewal_Report". THANKS IN ADVANCE!

    VB:
    Sub DateFilter() 
         '
         ' DateFilter Macro
         '
         
         '
        Sheets("QuickList_test").Select 
        ActiveCell.Cells.Select 
        ActiveCell.Offset(0, 4).Range("A1").Activate 
        Selection.AutoFilter 
        Selection.AutoFilter 
        Selection.AutoFilter 
        ActiveWindow.ScrollColumn = 2 
        ActiveWindow.ScrollColumn = 3 
        ActiveSheet.Range("$A$1:$P$6999").AutoFilter Field:=12, Criteria1:= _ 
        ">=2/1/2012", Operator:=xlAnd, Criteria2:="<=2/28/2012" 
        ActiveWindow.ScrollColumn = 2 
        ActiveWindow.ScrollColumn = 1 
        ActiveCell.Offset(0, -4).Range("A1:P7000").Select 
        Selection.Copy 
        Sheets("Renewal_Report").Select 
        ActiveWindow.SmallScroll Down:=-15 
        ActiveCell.Select 
        ActiveSheet.Paste 
        Selection.Columns.AutoFit 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th May 2006
    Location
    India
    Posts
    1,009

    Re: Filter by Date Range Copy and Paste to Sheet

    see sample data in the file "joben.xls" attached.
    sheet 1 has data. sheet2 has result
    if your sheet names are different modify the macro suitably.
    the macro is in vb editor module1
    if you want you can create a button from "form" toolbar (view(menu)-toolbar-form) and assign this macro to that button

    note the criteria dates are in F1` and G1 in sheet 1
    if different modify suitably

    VB:
    Sub test() 
        Dim r As Range, filt As Range, d1 As Long, d2 As Long 
        With Worksheets("sheet1") 
            d1 = .Range("F1").Value 
            d2 = .Range("G1").Value 
            .Range("A1").CurrentRegion.AutoFilter field:=.Range("A1").Column, Criteria1:=">=" & CDate(d1) _ 
            , Operator:=xlAnd, Criteria2:="<=" & CDate(d2) 
            Set filt = .Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible) 
             'filt.Copy
            With Worksheets("sheet2") 
                .Cells.Clear 
                filt.Copy 
                .Range("a1").PasteSpecial 
                .Range("A1:B1").EntireColumn.AutoFit 
            End With 
            .Range("A1").CurrentRegion.AutoFilter 
        End With 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    1st November 2011
    Posts
    4

    Re: Filter by Date Range Copy and Paste to Sheet

    Thanks for the Help!
    I didn't mention that I was running this from an "Entry" Sheet so the data was not present there. But I added another with statement moved it above the references to the sheet with the data "QuickList_test" and it worked. Other changes were the column of reference for the data and I added a step so that it would take me to that sheet. Again Thanks!!

    Finished Product:
    VB:
    Sub DateFilter() 
         '
         ' DateFilter Macro
         '
         
        Dim r As Range, filt As Range, d1 As Long, d2 As Long 
        With Worksheets("Entry") 
            d1 = .Range("F15").Value 
            d2 = .Range("G15").Value 
            With Worksheets("QuickList_test") 
                .Range("A1").CurrentRegion.AutoFilter field:=.Range("L1").Column, Criteria1:=">=" & CDate(d1) _ 
                , Operator:=xlAnd, Criteria2:="<=" & CDate(d2) 
                Set filt = .Range("L1").CurrentRegion.SpecialCells(xlCellTypeVisible) 
                 'filt.Copy
                With Worksheets("Renewal_Report") 
                    .Cells.Clear 
                    filt.Copy 
                    .Range("a1").PasteSpecial 
                    .Range("A1:Q1").EntireColumn.AutoFit 
                End With 
                .Range("L1").CurrentRegion.AutoFilter 
            End With 
        End With 
        Worksheets("Renewal_Report").Activate 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 6
    Last Post: April 30th, 2014, 08:59
  2. filter, copy and paste to new sheet
    By dadomi in forum EXCEL HELP
    Replies: 9
    Last Post: October 28th, 2011, 22:53
  3. Replies: 1
    Last Post: May 22nd, 2008, 14:56
  4. Run an Advanced filter, copy and paste to new sheet
    By alekmaxwell in forum EXCEL HELP
    Replies: 4
    Last Post: December 21st, 2004, 19:51

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno