Consolidation of various files based on cell value/Filter

Something like this:

Please Login or Register  to view this content.

Working with dates gets tricky, you'll have to play with the AutoFilter part to make sure the filter is being applied in the proper format, or that your [A1] cell is already in the proper format.

1) I can't tweak the code for the date formats unless I can see an excerpt from your worksheet and this report sheet we're creating.

2) Rather than hardcoding "always append", I've added a question to simply give you the option.

Please Login or Register  to view this content.

When activating an AUTOFILTER, the Field:=???? should be the column you want to apply the filter to. Since I'm activating the Autofilter on the entire first row, this makes the normal column "numbers" work. So Field:=29 in my original version simply means column AC.

Now, adjust that number to whatever the actual column number is that you want to apply the date filter to.

===========
Looking at your code in Tracker, the high level of redundancy can be used to greatly reduce the amount of code. For instance, you can feed an array of column numbers into the same 'test' over and over again to check each on for "blanks" instead of having to code each column test individually. For instance, here's a potentially streamlined version of one of your macros:

Please Login or Register  to view this content.

Notice the underlined array of column values? Each is tested one at a time. If all pass, then the AllDone variable stays TRUE and the validation completes with time stamps and username. But if one of them fails, the AllDone is set to FALSE and the loop exits there.

You can use that method so that each validation macro is essentially the exact same length as this one, just the array is different. For Validate_PO_Holds, the array would be Array(18, 3, 5, 4, 8, 15, 19, 20, 21), for Validate_Order_Creation the array would be Array(33, 3, 5, 4, 8, 15, 19, 20, 21, 16, 17), and so on.


You know, now that I think about this, you really only need ONE macro to do all of this validation instead of 50 different ones. If you can feed the array values in at the time the macro is called, then the same macro would work for all validation scenarios.

Perhaps a main validation macro called ValidateMain with a syntax like so:

Please Login or Register  to view this content.

Then the when you call this macro, you feed in the correct array of columns to check instead of different macros:

Please Login or Register  to view this content.

Since the MAIN_VALIDATE macro already activates the TRACKER sheet, that may be redundant on the the ValidateMain macro, too.

==========
Another way to remove all the formulas from select sheets:

Please Login or Register  to view this content.

Take a look through here. With no data to speak of to test on, I was editing blind, but this should get you 95% of the way there. If you like the new array method and the ValidateMain macro, you can delete all the other individual macros as redundant.

Originally Posted by aravindhan_31

...how about the consolidation from various files?

What about it? Isn't that what the original macro I gave you does? Make sure you edit not only the row to copy from, but also the Field:= to apply the autofilter to.

BTW, the Criteria1:= accepts formatted commands. Instead of having to convert all dates in all sheets to raw numeric format, just leave the source sheets alone and format the criteria to the correct date format found in the source sheets.

Please Login or Register  to view this content.