MS Excel 2003 macro to copy paste data in different sheets based on certain criteria

[bhaktprahlad]

Hi,

I have an excel sheet that contains ID and Name. Please see the attached excel sheet Macro Sample.xls

In it I have the main excel sheet called "Database" and I would like a macro to do the following:

a) To filter data in the database sheet. I want it to filter using the string length of the different "Name" column. Only if the string length is greater than equal to 3 should it filter the data by that criteria.

b) After filtering the data on that criteria it should paste it in the worksheet having the same name as the filtered criteria.

c) After pasting in the pertinent sheet the data it should group the files in that worksheet using the ID criteria. (Only ID less than 30 should be grouped using the Data-Group and outline-group option.

After copying in each cell it should stop.

This can be done by manually recording a macro and then running it. But this hard coding has the limitation that in case I have a new dataset not encountered earlier i would need to re-record the macro again.

Is there a smarter way to do this using VBA code?

I understand that looping can work wonders in this but how do I get it to work?

Thanking you in advance.

Warm Regards,
Kallol

[TMS]

For something as specific as this, I would normally start by recording a macro as I carried out each of the task(s). So, you'd have several macros for each of the steps ... these would be "hard coded" and do exactly the same thing with the same amount of data each time, not necessarily efficiently.

I would then look at how to generalise each macro/subroutine, for example, changing hard coded ranges to flexible ranges dependent on the number of rows and/or columns, etc. You should avoid using select, etc. Test each generalised macro to check that it still does the same as the original code.

I'd then create a Control macro/subroutine to call each of the other steps/macros ... and make all the subroutines other than the Control routine private so they're not run individually.

That's it, in very general terms.

I'd suggest you try this approach and come back to the forum if and when you need specific advice about the little stumbling blocks you may encounter.

Regards

[bhaktprahlad]

Hi TMShucks,

Thanks for the pointer. I will try that and revert.

Warm Regards,
Kallol