Need Filter+Export to PDF+Delete Filtered Items Macro

[michaeljoeyeager]

Friends,

Here is a screen shot of my workbook.

Pic.png

I need to create a "Closeout Job" function and I don't know how to go about it. This work book has a ton of entries so I need to start weeding it out. I don't need this list to contain completed jobs but I do need a record of them. I would like to be able to use the filter funtion to select a particular job... Export those entries to a PDF to keep for records and then delete all of those specific entries from the master list.

Here is my filter code and screen shot post filter.

pic2.png

Please Login or Register  to view this content.

How would I go about doing something like that?

Thanks in advance,

Mike

[tigeravatar]

Mike,

Maybe a new button on the sheet assigned to this macro? The Foramt(Now... is used to ensure no duplicate names are saved. You can add a more descriptive name than just the date and time if you'd like also:

Please Login or Register  to view this content.

[michaeljoeyeager]

Wow homerun again...

But I have a few questions if you don't mind. Using a modified version of the code that you gave me the other day I created this userform that populates correctly...

Job Close Out.png

I would like to use this form so that when OKButton is clicked, it does the filter, creates the PDF (and names it by the job number like ###-##-####.pdf) and then deletes those entries all at the click of that button.

And then also, is it possible to have that pdf include the cell comments? the information in the comments is important to include for records.

Then a quick aside...
I am new to vba and this forum and forums in general. Why are you so helpful? Are forum contributers compensated for all the help they provide or is it all done out of a generous spirit?

Thanks again for everything; all the help you have offered is really impressing my boss

-Mike

[tigeravatar]

Mike,

For the Job Closeout form, is the drop down used to filter a column? And is that column A (CSJ) or B (Project #) or some other column? Can the item chosen from the drop down be used as the save name (job number ###-##-####)?

As for cell comments, that could be possible, but it would complicate the code quite a bit. You'd have to extract each comment individually and write it to its own cell. I'd recommend using a new sheet, copying the filtered data to the new sheet, and then extracting the comments. I'm not sure where you'd want the comments in relation to the data for the PDF file.

As for why we're helpful? I can't really speak for others, but here are my reasons:
I started here to broaden and add to my skillset. I can do this by looking for new challenges I have not come across before within my skillset. The easiest way to find challenges is through a forum like this, where new users like yourself have a question and they aren't certain how to answer it. So I pick up the challenge and see if I can provide a working, satisfactory solution based on the question and its requirements. Usually I have to adapt what I already know to the problem, and perhaps do some research on something I'm not totally sure how to do, which accomplishes my original goal of broadening and adding to my skillset.

That alone keeps me posting, and has helped me learn so much since I started. Especially when there are multiple solutions offered and I get to see different ways of doing something. Helping people out is, in my eyes, a nice altruistic bonus. Most of the time we don't get any kind of thanks or recognition, but when we do its really appreciated

And I'm glad I can impress your boss vicariously through you, haha

[EDIT]
Forgot to add: No, we are not compensated. Everything we contribute is purely voluntarily.

[michaeljoeyeager]

Yes, the drop down is just populated by CSJ #'s. so upon selecting one and clicking OK, I would want a macro that filters out everything except entries with that CSJ, exports to PDF with comments somehow, and deletes those entries from the master list... All at the click of the OKButton with ScreeUpdating = False.

Is that possible?

Thanks,

---------- Post added at 01:31 PM ---------- Previous post was at 01:30 PM ----------

And Yes, The CSJ that is selected from the combobox would be the file name of the PDF

[tigeravatar]

Mike,

Yeah, that is absolutely possible. Here's some code you should be able to adapt. Let me know if you run into trouble. Code assumes the combobox is named cbo_CSJ:

Please Login or Register  to view this content.

[michaeljoeyeager]

Well I'm trying to follow the methodology here and I don't understand why you set strCSJ = 1 / 0
I assume that wasn't a typo and you are forcing an error which is sending compiler to

Please Login or Register  to view this content.

and then ok on err just exits the sub

[tigeravatar]

Oh, oops, lol. meant to delete that strCSJ = 1/0 line. I used it to test the error handler, and just forgot to delete that line before posting the code, sorry ><

[michaeljoeyeager]

OK tigeravatar,

I have been messing with the code that you gave me to try and get it to format the PDF just right. I have it to where it works now and does everything that it is supposed to but the coding is so clunky and slow. After I click OK it literally takes a good 2 minutes for the PDF to generate. Would you mind looking through the code and streamlining it a bit so that it runs more efficiently? I have tried to add as many comments as possible to help you follow my thought process.

I will also attach a sample PDF that is generated so that it will make more sense why I added the row and column labels

0718 - 01 - 052 - 02-Aug-12 11_23_24.pdf

[michaeljoeyeager]

Please Login or Register  to view this content.

[tigeravatar]

Mike,

After looking over the code, I see that it has a lot of Select statements. Those are completely unnecessary. For example, instead of:

Please Login or Register  to view this content.

It can be written as:

Please Login or Register  to view this content.

Very rarely is it actually necessary to select something to work with it. Select statements cause extra unnecessary function calls than can slow down code. I also see you use "For i = x to y" loops with ActiveCell.Offset(i...). Again, there's no need to use Select and rely on ActiveCell. Just work with the range directly:

Please Login or Register  to view this content.

That code will put the numbered sequence in the desired cells, without using a loop, and without using Select or ActiveCell statements.


Also, the majority of the code is now formatting. Formatting via VBA is just slow, not much can be done about that. Removing default value statements can help slightly. These are unnecessary for example:

Please Login or Register  to view this content.

Also, you can apply formatting to several cells at the same time, instead of one cell at a time. Just separate range groups with a comma (note that you only need to set the LineStyle to xlContinuous because the rest is default and doesn't need to be specified):

Please Login or Register  to view this content.

I also saw you have screeupdating turned off and back on again before all code and after all code. While it won't impact speed at all, that is unnecessary because during the "With Application" blocks at the beginning and end of the code screenupdating is already getting turned off and then back on.

[michaeljoeyeager]

Awesome. Those adjustments sped it up a bit, but now I have another problem. In the PDF that I sent you, it had a cell reference and all of the comments at the end of the PDF. Now for some reason it will not include the comments anymore. I have added this and it still won't work...

Please Login or Register  to view this content.

[tigeravatar]

Mike,

Make sure you're using the correct sheet. Don't use ActiveSheet because it is usually unclear which sheet is the currently activesheet, especially with screenupdating turned off.

In the code, there is a With Sheet.Add line. Just use that:

Please Login or Register  to view this content.

[michaeljoeyeager]

whelp... you were right as always... I feel bad because I can't give you any reputation because you are the only one who ever answers my questions!

Take care. Unitl next time.

-Mike

[tigeravatar]

You're very welcome