VBA to print specific data sets

**ndfrose** · 06-27-2011, 06:35 AM

Ok.

I have a spreadsheet in which there are 2 worksheets. The first is a data sheet. the second is a sheet that uses a vlookup function in combination with a drop down list to pull data from the data sheet. this data is then used to populate the relevant cells in the second sheet.

the drop down list represents 30+ different scenarios\data sets.

ok - i think thats fairly straight forward enough.

What is bugging me is the fact that I have to print off the second worksheet for reports we send out to clients. Currently, i click the drop down box, click scenario 1, and then go file -> print.

Repeat for scenarios 2,3,4,5 and so on and so forth. I have to do this on a fairly regular basis. Also - its not always the case that I have to print every scenario (ie it may be that i require to print scenarios 1,9, 15, 22 etc etc) depending on the clients request for example.

So - my question is: Is there a way to employ VBA code where I can click a button embedded in the spreadsheet that brings up a list of the scenarios in which i can 'tick box' the scenarios i want printed out (Preferably with the quantity of copies as an option somewhere also) and then tell it to print?

Im using excel 2003 (sadly) and Win XP.

If anyone could tell me whether this is possible then I would appreciate it. If you have any pointers towards relevant bits of VBA code etc then that too would also be helpful.

Any questions then just get in touch.

Cheers.

**maczaq** · 06-27-2011, 08:52 AM

Hi,

I think you will have to adapt my solution into your needs but I think I provide simpliest solution as possible.

I created this script under excel 2003

Best Regards

**ndfrose** · 06-28-2011, 03:38 AM

Originally Posted by maczaq

Hi,

I think you will have to adapt my solution into your needs but I think I provide simpliest solution as possible.

I created this script under excel 2003

Best Regards

maczaq

thanks for this - not really had a chance to try it out yet but will hopefully have a chance to check it out later today.

**ndfrose** · 06-30-2011, 04:43 AM

Originally Posted by maczaq

Hi,

I think you will have to adapt my solution into your needs but I think I provide simpliest solution as possible.

I created this script under excel 2003

Best Regards

Hi. Sorry for the delay. Have been busy.

Thanks for the spreadsheet. It almost what im looking to do.

Whilst your example sheet has a row for each of the data sets pulled from the data sheet, and your vba code seems to reflect that, i need only one data set at a time.

The reason for this is the nature of my spreadsheet.

Firstly, the sheet displaying the data sets will only ever be able to show one data set at a time. The data set shown is dependant upon the chosen item from the drop down list.

it is important to note that if i choose scenario 1 from the drop down list, the relevant data is displayed. Should i then go to the drop down list and choose scenario 2, the same cells displaying the data for scenario 1 are repopulated with data for scenario 2. the same goes for the remaining scenarios.

So basically - what im looking for is vba code to automate the following process:

1. click a button in a spreadsheet to begin process.

2. window pops up with a 'tick box list' of all those scenarios available (as detailed in the drop down list in my spreadsheet)

2. I tick all of the scenarios i want printed off

3. the code then automates the process of populating the spreadsheet with the first of my chosen data sets, printing it, repopulating the sheet with the second of my chosen data sets, printing it, repopulating it with the third of my chosen data sets, printing it....etc etc.

A step in there to confirm the print area and chosen printer would also be particularly delicious.

Is this possible? is it simple. could a newbie like me do it with pre written vba modules?