Automate vlookup to create New worksheets based on selection criteria

Hi,

I want to automate the following process IN excel 2007:-

Worksheet 'Product' is the master list and Sheet 1 contains the list of numbers which needs to be matched with Column F in Product sheet .

Based on the matching values of column F in product worksheet and column A in Sheet1,I want to create separate worksheets for the rows from Product worksheet based on selected values of column AC.

For example,
All the matched column F rows having similar values in column AC should appear in one worksheet and likewise for every unique value of column AC for the matched rows,separate worksheets should be created.
The new worksheets should contain only column values,F,K and Y-AD from product worksheet for matched rows.

Hope I have been able to explain myself clearly.Any help in this reagrd would really be appreciated.

Thanks,
Kolokor

Try this one out. I think I understood you. When you run it, it will create sheets that are named after then entries in column AC. There is a palce to add column headers if you need to.
Product.xls

Hi Blane,

Thanks a lot for your help.Your code has been of great help.I have only one query.

Please Login or Register  to view this content.

I am very new to VB hence I need your guidance as to how to populate the column headers with code besides making the value of n as 1 from 0 as is evident from the comment.

Thanks

Originally Posted by kolokor

Hi Blane,

Thanks a lot for your help.Your code has been of great help.I have only one query.

Please Login or Register  to view this content.

I am very new to VB hence I need your guidance as to how to populate the column headers with code besides making the value of n as 1 from 0 as is evident from the comment.

Thanks

This will add column headers and set the last used row to 1. I don't know what you want in the headers, so change the text as appropriate.

theSheet.Cells(1,1).value = "F"
theSheet.Cells(1,2).value = "K"
theSheet.Cells(1,3).value = "Y"
theSheet.Cells(1,4).value = "Z"
theSheet.Cells(1,5).value = "AA"
theSheet.Cells(1,6).value = "AB"
theSheet.Cells(1,7).value = "AC"
theSheet.Cells(1,8).value = "AD"
n = 1

How's that?