How to mend the excel macro that helps me scheduling movies

Dear Members,

I would like to ask the help of this group. I am working for a multimedia-broadcasting company and one of my main task is producing TV-schedules. I am doing it in excel, I have an excel sheet for every day of the month which I fill up data from other excel sheets (so called master libraries). My question would be: is it possible to create an excel template that can automatically fetch the data from different excel sheets? It would be great if I could give a range of the data which it should look up and than it would pick a random movie for copying.
I've created an example Excel workbook, please find it here: https://dl.dropbox.com/u/48496201/Example_1.xlsm

I have five worksheets in the example: Action, Thriller, Documentary and Sci-Fi for the different movies and one for the schedule template. In the template I have blocks for the movies, different movies have to be aired at different times. The first one has to be a Thriller, the macro for the first slot is:

Please Login or Register  to view this content.

My problem is that I do not know how to tell that one film has to be picked from the Range.
I also want to add a line that says if the one movie is picked is bold, than search for an other within the range until it finds one which is not bold. After it copies to the template, make it bold both in the template and in the worksheet so it is not going to be scheduled again.

I would like to use pretty much the same macros for the other slots, I only have to adjust them so they pick the correct workbooks and correct ranges (and the correct number of movies within the ranges since there are slots for five movies).

Can someone help how to mend this code? Or instead of working with ranges, is there an better solution? Any feedback is appreciated!

Thank you,
Attila

Hi,

this code should work, give it a try. If there's something wrong with it, please tell me.

Please Login or Register  to view this content.

Hello Gergo,

thank you for your reaction! I've tested your code, in the last but one row I've corrected ture to True.

Please Login or Register  to view this content.

The mechanism that picks the movie from the Thriller sheet and copies it to the Template works perfectly!
With the rest, I have a little issue: It should only pick movies from the Thriller sheet which are not bold. Unfortunately, that doesn't work now, I've tried out to bold a lot of titles and run the macro, it picks also movies which were already bold before the picking.

Perhaps can you help me with this?

Thank you,
Attila

Hi,

thanks for correcting.

I found the mistake in my code, I referenced a range from the incorrect worksheet. It should work now. I also erased the array at the end of the procedure, because it's good practice according to experts. (Frees up memory and maybe other benefits?)

Please Login or Register  to view this content.

Hi Gergo,

This code works perfectly!

I use an extension of your code so it can pick five random movies from the array and copy it in the template. Here it is:"

Please Login or Register  to view this content.

Thank you so much for your time!

Greetings,

Attila