Excel Keyword List *Create Brilliance* Code

[stepsidestroker]

Hi, i'm new to this forum but always find it very helpful finding answers through the support of other forum users, so I appreciate everyones help.

I have been working on a project to try and automate/simplify a recurring process at work. I have a database that I can pull from and copy to an excel spreadsheet as needed, so, because the database continually grows, it is necessary to do on at least a monthly basis. I can copy the data into a working spreadsheet easy enough, but once I get the data into an excel spreadsheet, I need to automate the following functions:

1. Use a predefined keyword list containing approximately 20 keywords, which will grow over time and need to be added to
2. Create a new worksheet for each keyword in the list
3. Delete the existing data in each of the keyword worksheets each time the code runs
4. Copy column headers from data spreadsheet into each keyword worksheet
4. Search my data spreadsheet for instances of the keyword within a sentence or string in Column K
5. For each match of the keyword, copy that entire row of data into the corresponding worksheet
6. Rather than adding onto the last row the next time I run the script, I need to start out with blank keyword pages each time.

Basically, the string of data in my main worksheet contains the data I need to break up into individual worksheets, but because its a large string of data, I cannot simply do an a-z sort.

Hopefully this is as clear as mud and someone who is much better than I is able to make some sense of this process.

Thanks in advance,
Brian

[ragulduy]

Would be a lot easier with an example workbook to look at but I'd guess:

Please Login or Register  to view this content.

Based on your keywords being on a sheet called "Keyword List" in column A and your header information being on a sheet called ("Data") in row 1.

[stepsidestroker]

Hi and thanks for such a quick response. I renamed the worksheets Data and Keyword List. When I created the module and pasted the code in, the 3rd line "Set kword_list = Sheets("Keyword List").Range("A1:A" & Range("A" & Rows.count).End(xlUp).Row) For Each kword In kword_list" shows in red and therefore errors out. Is there a step I am missing or did I possibly mislabel something?

Thanks again

[stepsidestroker]

Ok I created a new copy of the database and reinserted the code. Now it ran properly, created the tabs I want, placed the headers appropriately, and copied the appropriate data accordingly so thank you for that!

One problem, however, after the first run, I am getting a VB error in kword_sh.Name = kword (third line from end of script), highligting in yellow. It looks like its a problem with the newly created worksheets (named from the keyword list). It appears that because they are created, it will not recreate them so it errors. I went through and deleted all the worksheets that werent Data or Keyword List, then it gave me the error on the worsheet Keyword List. Seems like we need to do something to say "if exist then" or "if not exist then"...but I know very little/nothing about VB code.

[ragulduy]

It already has an if exist then/if not exist then method (it tries to set kword_sh to be a particular named sheet, then has error handling to create that sheet if it doesn't exist).

I'd guess there is an issue with what the keyword list values are. Some things I can think of that would cause an error at that line:
-You are trying to name the sheets something invalid (for example, too long a name or containing characters which cannot be used)
-You are trying to name the sheets with numbers I think you could fix that by changing:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.