Incredibly Cumbersome Code!

Greetings again, Gurus. It's 9:00 pm on Saturday night here in good ol' Tennessee, and I'm still working. Ain't life grand?

Here's my deal. The workbook I have attached gives me the exact end results I want, but since most of the formatting was recorded, the code is incredibly long. I'm guessing one of you guys will have a field day, and laugh your **** off, reviewing this code.

My goal is that the buyer take the one master sheet they receive from the supplier, extract the appropriate information needed to two seperate sheets and format them to print neatly. The buyers will use the printed sheets to go into the purchasin program on their computers and make changes to the purchase order. The page set up is so cumbersome because the original worksheet received is never formatted the same, some of these are coming from China, and even the type of paper is set wrong.

The workbook is attached. If you guys want to take a crack at cleaning it up a little, I would appreciate it.

Have a good one!

Jerry

Thanks, Mike. I'll clean up the 'set title' section for each sheet. If I'm correct, and get rid of defaul formats, the code below should work:

Please Login or Register  to view this content.

That tip will help trim it down some. Any other suggestions? Anybody?

I've cleaned up the code to the best of my ability, using Mike's tips as a guide. I've tried several other little changes, but I usually seem to screw up my output.

I've attached the workbook, if anybody wants to take a look at it, and here is the code.

Please Login or Register  to view this content.

Thanks for any help or suggestions offered.

Jerry

Yes, Roy, both of my end sheets are fromatted the same.

Originally Posted by [email protected]

I've cleaned up the code to the best of my ability, using Mike's tips as a guide. I've tried several other little changes, but I usually seem to screw up my output.

I've attached the workbook, if anybody wants to take a look at it, and here is the code.



Thanks for any help or suggestions offered.

Jerry

Hi,

Just a general observation. Clearly a lot of that code was captured through the macro recorder. That's fine when starting out or getting the syntax for sonething you can't quite remember, but the big nuisance with the recorder is that it captures absolutely every parameter available and uses the default setting property. I find that most of the time default properties are fine and hence you can delete just about everything you've recorded apart from any where you don't want the default.

You commented about why is took so much code to set the border. That's a classic case. The recorder captures all four sides of a border. If you're happy with a fours sided border then replace all the four sets of code with

Please Login or Register  to view this content.

It is quite a long procedure and I suggest for readability if nothing else it would be better broken down into separate procedures, calling them one by one from a master control procedure, or in a chain from each procedure in the chain.

HTH

I suppose it would be possible to format a sheet with the correct column widths and such in the workbook the sub will be called from. I would still have to select the "data cells" in each sheet, after the filtered data is imported, to format them with borders, as that range will never be the same.

Also, regarding the .BorderAround procedure, that won't work, as I'm not really placing a border around the data, rather using borders to seperate the data for easy readability. Is there a shortcut for placing a border around every cell selected, like the grid, rather than just a border around the outside of the entire selection? This would give me nearly the same result, with the exception that I don't currently place a border around the outside of the selected range.

Thanks for the help guys.

Jerry

Originally Posted by [email protected]

I suppose it would be possible to format a sheet with the correct column widths and such in the workbook the sub will be called from. I would still have to select the "data cells" in each sheet, after the filtered data is imported, to format them with borders, as that range will never be the same.

Also, regarding the .BorderAround procedure, that won't work, as I'm not really placing a border around the data, rather using borders to seperate the data for easy readability. Is there a shortcut for placing a border around every cell selected, like the grid, rather than just a border around the outside of the entire selection? This would give me nearly the same result, with the exception that I don't currently place a border around the outside of the selected range.

Thanks for the help guys.

Jerry

OK, instead of the .BorderAround, how about:

Please Login or Register  to view this content.

Rgds

Thanks, Guys. Roy's macro does everything, except that it titles both sheets "Quantity Changes" in cell F3. I can pull that part out of the "format" sub, and put it into the "filter" sub, add the code to title the other sheet "Price Changes" and I'm good to go.

Have a good one guys.

Jerry