Filter based on range and then copy to new workbook

[samst]

HI
Wondering if anyone can help with the following:

I need to filter a sheet based on a column date range and copy it to a new workbook sheet in the first blank row.

So for all cells in column I of sheet1 of workbook1 that have a date that is within the last 7 days copy that row (only columns A through U) and paste them into sheet1 of workbook2 in the first blank row

Thanks!

 

[Datsmart]

I would suggest using Advanced Filtering.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html

AdvancedFilter Demo
http://www.contextures.com/xlVideos04.html

Another good link with sample VBA code here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet. The original data remains unchanged and the data "Filtered" to another page can be used as desired.

 

[samst]

Thanks Datsmart
I was hoping for a vba solution if possible so that it can be automated. Would you be able to help?

 

[Datsmart]

Sorry for the late response. Just getting back to this now.

The third link I gave you has some example code to automate using Advanced Filter.

What have you tried so far?
Any code at all?

I put together some code that works on my two test workbooks.

The code below assumes you have opened your Source data workbook first and run the macro from there.
Code then assumes you have the second "Target" workbook already open in the background and the "Source" workbook is active.
You need to have a Criteria Range setup in the Source workbook in a worksheet called "Sheet2". On that sheet are the same Header lables as your Source data.
There need to be Two columns for the Date, Column "I" and Column "J". The code fills in the critera for todays Date in cell I2 and a second date 7 days from that date in cell J2.
After copying to the second workbook, the code activates that workbook and that worksheet.

Sub FilterToAnotherBook()
Dim FilterRange As Range, Criteria As Range, TargetRange As Range
'Assign Workbook Variables
    VarW1 = ActiveWorkbook.Name
    VarW2 = Workbooks(2).Name
    If VarW1 = VarW2 Then VarW2 = Workbooks(3).Name
    If VarW2 = "" Then
        MsgBox "You must have your Target WorkBook Open."
        Exit Sub
    End If
'Assign WorkSheet Variables
    VarS1 = "Sheet1"
    VarS2 = "Sheet1"
'Clear Target range of old Data
    Workbooks(VarW2).Sheets(VarS2).Cells.ClearContents
'Be sure you are in the proper book and sheet
    Workbooks(VarW1).Sheets(VarS1).Select
'Assign Date range to Advanced Filter Criteria cells
    Sheets("Sheet2").Range("I2").Value = ">=" & Date
    Sheets("Sheet2").Range("J2").Value = "<=" & Date + 7
'Set variables to specific Ranges
    Set FilterRange = Workbooks(VarW1).Sheets(VarS1).Range("A1").CurrentRegion
    Set Criteria = Workbooks(VarW1).Sheets("Sheet2").Range("A1").CurrentRegion
    Set TargetRange = Workbooks(VarW2).Sheets(VarS2).Range("A1")
'Do Advanced Filter using variables
    FilterRange.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=TargetRange, CriteriaRange:=Criteria
'Clear memory of variables
    Set FilterRange = Nothing
    Set Criteria = Nothing
    Set TargetRange = Nothing
    Workbooks(VarW2).Sheets(VarS2).Activate
End Sub

 

[plaroche2]

I would like to be able to run this filter twice on the same dataset with different criteria and paste each filter's results in different worksheets in the same workbook. Is this possible?

 

[Datsmart]

I would suggest adding lines of code after the first Advanced Filter to re-set the Criteria to your second range.
Re-assign the worksheet variable (TargetRange) to the second destination, then repeating the Advanced filter section of the code.

 

[plaroche2]

John,
As I looked at the code above it is not quite what I am looking for. The code below is actually from the Mr.Excel book VBA and Macros for Microsoft Excel. The code is creating a report for each customer in a data set. I would like to create two reports or more for each customer using the same source data but reporting on it differently in different worksheets. How can this code be modified to do so? Do I have to set up different For Each... loops or include all the advanced filter methods within one loop? I can usually find code out that serves my basic purpose and modify to fit my needs but I have not found any postings concerning this issue. Thanks, Phil

Sub RunReportForEachCustomer()
' Page 231 through 233
Dim IRange As Range
Dim ORange As Range
Dim CRange As Range
Dim WBN As Workbook
Dim WSN As Worksheet
Dim WSO As Worksheet

' Since this is called from a button on Menu,
' first select the sample data sheet
Worksheets("SalesReport").Select
' Clear out results of previous macros
Range("J1:AZ1").EntireColumn.Delete

Set WSO = ActiveSheet
' Find the size of today's dataset
FinalRow = Cells(65536, 1).End(xlUp).Row
NextCol = Cells(1, 255).End(xlToLeft).Column + 2

' First - get a unique list of customers in J
' Set up output range. Copy heading from D1 there
Range("D1").Copy Destination:=Cells(1, NextCol)
Set ORange = Cells(1, NextCol)

' Define the Input Range
Set IRange = Range("A1").Resize(FinalRow, NextCol - 2)

' Do the Advanced Filter to get unique list of customers
IRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=ORange, Unique:=True

FinalCust = Cells(65536, NextCol).End(xlUp).Row

' Loop through each customer
For Each cell In Cells(2, NextCol).Resize(FinalCust - 1, 1)
ThisCust = cell.Value

' Set up the Criteria Range with one customer
Cells(1, NextCol + 2).Value = Range("D1").Value
Cells(2, NextCol + 2).Value = ThisCust
Set CRange = Cells(1, NextCol + 2).Resize(2, 1)

' Set up output range. We want Date, Quantity, Product, Revenue
' These columns are in C, E, B, and F
Cells(1, NextCol + 4).Resize(1, 4).Value = Array(Cells(1, 3), Cells(1, 5), Cells(1, 2), Cells(1, 6))
Set ORange = Cells(1, NextCol + 4).Resize(1, 4)

' Do the Advanced Filter to get unique list of customers & product
IRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CRange, CopyToRange:=ORange
' Create a new workbook with one blank sheet to hold the output
Set WBN = Workbooks.Add(xlWBATWorksheet)
Set WSN = WBN.Worksheets(1)

' Set up a title on WSN
WSN.Cells(1, 1).Value = "Report of Sales to " & ThisCust

' Copy data from WSO to WSN
WSO.Cells(1, NextCol + 4).CurrentRegion.Copy Destination:=WSN.Cells(3, 1)
TotalRow = WSN.Cells(65536, 1).End(xlUp).Row + 1
WSN.Cells(TotalRow, 1).Value = "Total"
WSN.Cells(TotalRow, 2).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
WSN.Cells(TotalRow, 4).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

' Format the new report with bold
WSN.Cells(3, 1).Resize(1, 4).Font.Bold = True
WSN.Cells(TotalRow, 1).Resize(1, 4).Font.Bold = True
WSN.Cells(1, 1).Font.Size = 18

WBN.SaveAs "C:\" & ThisCust & ".xls"
WBN.Close SaveChanges:=False

WSO.Select
Set WSN = Nothing
Set WBN = Nothing

' clear the output range, etc.
Cells(1, NextCol + 2).Resize(1, 10).EntireColumn.Clear
Next cell

Cells(1, NextCol).EntireColumn.Clear
MsgBox FinalCust - 1 & " Reports have been created!"
End Sub

 

[Datsmart]

I would suggest you start your own thread.

Include your code, (please use code brackets), and give us an example of how your data is laid out. It is hard to tell what the code variables become as it is run without knowing what you have.