Wrote code to print worksheets based on numbers in cells and rows, but is it efficient?

Hello, I started learning VBA for the first time a week ago so please forgive my lack of knowledge. I am having a lot of fun with the added functionality.
This is the first piece of code I am trying to write from scratch as well.

My workbook has customers who's inputs will each fill out different forms but some of them will share the same forms (templates). My goal with this code is to have a workbook which prints specific sheets based on numbers (or references to sheets) in the cells next to the customer's name. The customer is picked by a dropdown menu (Named that range "Customer"). This data will be in a data sheet hidden later on. The reason for this, is that I want it to be easy for someone else to add a sheet later on and have it print for the specific customer simply by adding the number to the list next to their name.

This is what I came up with and it seems to work! But, being so new, I don't know if I am doing this as efficiently as I could be. Moreover, I am having trouble understanding how not to use the .select and .activate methods. I know I am supposed to work with Objects directly, but in some cases where I used .select I can't think of how not to use it.

Please Login or Register  to view this content.

It took me a while to figure out how to get all prints to be one job and for the selection to NOT include the data sheet as well. Lastly, I am having trouble understanding how to use arrays. Should I be using an array for this instead? I just want make sure I am creating something robust and efficient. I am happy to be a part of this forum and I definitely appreciate everyone's time and help.

I tried uploading the workbook, but it continually tries to upload without actually doing it. (Maybe my work network or something? It's only a 37KB .xlsm)

Hi,

You could try using dropbox or something else to add your file. Its better to debug and work with if I have the actual workbook (I am not an expert)

Hi, Thanks for your response! I turned off enhanced uploading and it worked! I was using multiple modules to try different things, but the one I am using for this code is Module 4.


EXPORT PROJECT TEST BOARD.xlsm

Hi,

See if this helps.

Please Login or Register  to view this content.

By the way this code uses another loop for the sheets because you are using numbers alone for the actual name of the sheet and it conflicts with the sheet index.

Thanks

In this example the sheets are name S1, S2... and so on instead of 1,2,.. so you can get rid of that second loop that would slow you down

Please Login or Register  to view this content.

Thanks

Thanks for the reply! I tested out the Sub and while it does print the sheets in the range, it prints out the whole currentregion. The difference from mine is that mine only prints sheets for the customer that has been selected in the drop down box and also mine makes it only one print job (which is my goal as well).

However, you code does show me that I can probably remove one of my For Each loops (which loops through sheets) and replace it with an IF THEN with Sheets.Printout.
The way I got it to be one job is by using an IF THEN and Select or Select (false) to make a selection of the sheets and then print selection.

So maybe if I do and IF THEN with Sheets.Select / False then print I can reduce the amount of code...

Thanks for your help!

I am sorry I forgot about that. This should do it

Please Login or Register  to view this content.

Hi Fredlo,

Thank you, I appreciate the effort. Unfortunately, this code still prints the sheets as different print jobs, but it does a good job of selecting which customer criteria to print.
I will definitely take from that, however, my code still has fewer variables. I'm not sure if that is really good or bad tho, because my code uses active and select more, which I am still confused about. Is there a way my code could be re-written to not use .Select and .Activate? That way I could better understand how to avoid it.

Thanks!

Hi,

I am not an expert but Look at what I point out in my code

Please Login or Register  to view this content.

Thanks

I see, that helps me to understand much better! Thank you!

So, in looking at your code. How would you get this part to print as one job?

Please Login or Register  to view this content.

Basically, why I felt I had to use .Select here is that the For Each loop goes through all the worksheets and when the condition fits it prints. To me .select was the only thing I could think of that could essentially store the sheets in a "staging area" until they were all chosen and ready to print.