macro to copy data based on criteria

**arnab0711** · 02-19-2011, 09:28 AM

Hi,
I have this excel sheet,where I have an "Input" page where I have two criterias
Project Desc& Item Description which I will input
Now at the click of "SHOW DATA" Button,it will filter data based on those criterias in the "raw data" sheet,and copy them to another sheet

**watersev** · 02-19-2011, 05:17 PM

one of the options to do that, save attachment and make sure the following reference is on:

1. [Alt+F11] - open Visual Basic window
2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
3. enter data and press the button

Please note that sheets names are hardcoded in macro. In case you change sheet names you'll need to change sheet names in the code as well.

**arnab0711** · 02-20-2011, 02:10 AM

Hi,
First of all thanks so much for the solution,but I wanted to add another criteria in the "Input" sheet,pl advice what will be the code.

**watersev** · 02-20-2011, 11:21 AM

hi, arnab0711, basically adding another criteria is rather simple. The syntaxis is the following:

Please Login or Register  to view this content.

T1.F18 - column of the data table we want take data from, here it is "Indent Unit Price". If it's text we are looking for UCASE is used to both "K" and "k" results. If there are several columns to search for we use AND:

Please Login or Register  to view this content.

'%K%' - what we are looking for in that column (appearence of K).
I've added another search parameter for you. As you did not specify for which column should it be, I've done that by myself as an example.

Hope this helps.

**arnab0711** · 02-20-2011, 12:42 PM

Hi,
The third criteria will also be the Item descriptioncolumn,basically it would work as the contain filter option where use (contains .... and contains ....)