building a macro that moves entire row of data based on criteria look up in three columns

Do you want the movement to happen as soon as the value is populated or on the click of a button?

Should all 3 columns have the same value?

Why dont you attach a sample file?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Hi,

In essence this boils down to filtering your data.
Assuming your data is contiguous and in Sheet1 columns labelled 'A', 'B' & 'C' with the data in A2:Cn where n is the last row of your data.

In Sheet1 enter in E1:G1 the letters 'A', 'B' & 'C'
In E2, F3 & G4 enter the word "DUE"

In Sheet2, the output sheet enter 'A', 'B' & 'C' in A1, B1 & C1

Now your macro is

Please Login or Register  to view this content.

See attached. Just click the big blue button on the Due Screening sheet.

Hi,

It's always good practice to use named ranges for all sorts of reasons rather than cell references like A1:B2, not least because they are more immediately understandable.

In addition if a range of data is likely to change in its number of rows, (which is usually the case), then it makes even more sense to make that name what we call a dynamic range name. If you look in the Formulas tab you'll see exactly how the range name 'Data' is defined.

The 'DataOut' range name is simply the column header labels from your data that you want to use in your results sheet.

The 'Crit' Range name is the range of cells that contain the selection criteria that you want to use to identify the rows that should be filtered and copied. In your case they are 'OR' conditions, i.e. the word Due can be in any column, hence the criteria range needs to be spread across three rows 2:4. Had you wanted the 'AND' condition where all three columns MUST contain the word Due you would have put the criteria word 'Due' on the same row in each criteria column.

Originally Posted by Nicklebach

....
I have also just converted from an IBM to a MAC, so the learning curve is pretty steep at the moment, but I am still plugging away at figuring out the excel mac version to figure out the named ranges that you used! All is good.

You have my sympathy. I'm a devout supporter of all things Mac and use a Macbook for many things - with the significant exception of Excel. I find the Mac version of Excel is just too clunky and difficult to work with. Particularly when it comes to macros & VBA where not only is some of the syntax different but also the Visual Basic editor which is nowhere as near efficient as the Windows version and makes developing and debugging code extremely tedious. So for any serious developments I always use Excel for Windows.

Hi Richard,
I'm Back
I need your ever patient and talented help please.
1) I broke the macro. I changed the criteria and added three individual filter tests. none of them seem to work, or bring over what I am asking them to do. So frustrated and such a beginner with macros. Please be patient with me :-)

2) Is it possible to build the Macro based on an inputed value from the user? For example, lets say there are too many people with Cervical 'Due' exams, can we add another criteria of lets say their birthmonth. User would press the filter data and get prompted for a birth month, then the rows specific to those who are due would get moved over to a seperate sheet.

3) I need to create mail merge letters from the filtered sheets. Can you create a macro to make a mail merge? I need to build these files as templates, as long as the excel and word document are in the same file location. This should work, no?

I appreciate your guidance through this, I am still on a Mac, although surviving, I am pretty frustrated.

Cheers,
N
AHS test-1.xlsm
Claresholm Medical Clinic Letter.docx

Hi,

Unfortunately I'm unable to respond in detail just at the moment - see my PM.

It is of course possible to do all you want with a macro so hopefully someone should be along to help before long.