Solved: Transfer data from one sheet to another based on criteria

[SteveG]

Hello,

I have data that is imported into an Excel file that contains information for several clients. Within that same file I have a worksheet that calculates out different statistics for each client based on a user selecting the client from a drop down in cell A12. This involves a ton of formulas to calculate the data based on client. Additionally, the data being imported contains a lot of information I don't need. What I'd like to do is be able to copy and paste the data from the import sheet to a calc sheet based on different criteria.

1) Client ID in A12 of Calc sheet found in column C of import data sheet matches.
2) Only if data in column D of import data sheet matches one of 63 criteria identifed as a Named Range "Transactions".
3) Insert data beginning on A17 of Calc Sheet

The import sheet range is A:L and the destination range for the Calc sheet is A17:L65536.

So, the user selects the Client ID in A12 and clicks a button to import the data for that client from the import sheet. I'm not exactly sure where to start on this one. Any help would be appreciated!


Steve

[stanleydgrom]

Steve,

This may get you started in the right direction.

If you are querying your data on a regular basis you can use a macro which will run Data, Filter, Advanced Filter. The macro is triggered by a command button.

See this link:
http://www.mrexcel.com/board2/viewtopic.php?t=291389&highlight=

Have a great day,
Stan

[SteveG]

Thanks for the reply Stan. I tried something similar to that using just the Macro recorder while applying the AdvancedFilter and it works to some degree but for some reason it is pulling in data that I am not expecting.

I have my criteria set up in sheet Filter_Criteria A1:B64. The data in column A is the same (my client ID). Column B is a list of 63 different transactions I want to retrieve the data for.

A B
1 ID1 TT1
2 ID1 TT2
3 ID1 TT1
4 ID1 TT3
5 ID1 TT1

Source data is in sheet Import&CLEAN range A1:L21686 but the range can vary in length each time it is imported so I am changing that using a TextBox although I'd like to have the code determine the range.

When I run the macro it pulls in all of the data just for the client in A but pulls in all transactions not just those listed in B. Below is the code I recorded and am trying to modify.


Sub AdvancedFilter()
Range("A17:L50000").Select
Selection.ClearContents
Sheets("Import&CLEAN").Range("A1:L21686").AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Sheets("Filter_Criteria").Range("A1:B64"), CopyToRange:= _
Range("A16:L16"), Unique:=False
Range("A1").Select
Calculate
End Sub


Any suggestions?

Thanks,
Steve

[SteveG]

Here is the macro I ended up with using Stan's suggestions.



Option Explicit
Sub Filter()
Dim DataTable As Range
With Sheets("Import&CLEAN")
Set DataTable = .Range("A1").Resize(.UsedRange.Rows.Count, .UsedRange.Columns.Count)
End With
DataTable.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), CopyToRange:=Range("Extract")
Range("A1").Select
End Sub




Thanks Stan!

Steve