VBA: Total sum range

Dude. This has been viewed over 400 times, but no answers. We need to see a bit more of your workbook and code to be able to offer any help.

If you have a look at the yellow band at the top of the page it'll explain how to post a (cut-down, desensitised) version of your workbook so we can look into helping you.

Tim

If you make the minor changes (below) the sub will just print out what's visible. That means you can use the Status and Past Due etc buttons to do the filtering, and just print-preview the table holding those data.

Please Login or Register  to view this content.

Was that the intention? I'm not sure what you mean by "Add total of printed". You could display the "totals" row in the table and include that in your print, but unfortunately, non-contiguous ranges always print on separate pages, so it won't make for a neat and tidy print-out.

Tim

Have you "PageDown'ed"? Non-contiguous ranges show on separate pages - and it seemed to work when I tried it before posting.

On the attached, hit "Past Due Age" 1-30 days, then hit "Print Dues". My result is 2 pages (was three, but I tweaked it) Page 1 is the data range, Page 2 the totals row.

Tim

Duh... sorry, my bad. Had another look and improved. Should be this:

Please Login or Register  to view this content.

This also fixes the issue with it printing on multiple sheets (I've no idea why I didn't realise what was happening before!)

I'll re-upload the attachment here.

Tim

NB forgot to say, you could make the print visually larger if you don't need all the columns in the table to be printed. E.g. instead of just generating the print preview, you could do this instead:

Please Login or Register  to view this content.

Only you can decide what you need, but hiding columns while forcing the print into one sheet wide will certainly make the writing clearer.

Tim

Calculating the subtotal separately would be a pain. You'd have to copy the data to be printed to a separate, temporary worksheet, insert the calculations, set columns widths and print range, print that sheet and then delete it. You could, of course, set up a print template for that purpose and leave it sat there. If you create totals using "=sum(Ax:Ay)" it will count add all values, not just those visible.

Personally, I'd keep it simple and use one of these methods to insert new rows into the table and let the worksheet do everything else itself:

• Hit [TAB] from the last cell in the data range of the table (J37 in the sample you provided)
• Right-click any cell in the table and hit "Insert" -> Insert table row. It'll be sorted later anyway.
• Grab the little green corner of the table (bottom right of the Totals row in column J) and drag it down however many new rows you need.

Tim

Sorry, I've absolutely no idea what that means because I have absolutely no context for that formula. Anyway...

I've created a print template in the attached file and linked the print button to it. Obviously the print template could then be hidden (or xlVeryHidden to hinder meddling) after you've set it up the way you like it.

The print range is simply the table, but you'll see in the VBA code you can enter headers and/or footers for the print too. I suppose you could adjust the print area to include row(s) above the table and put a title or whatever there, too. Study the code so you know what's going on and how to adapt it!


Tim

You have to do it either manually in the VBA environment, or in code.

Open up the VBE and hit F4 (I think) to bring up the Properties page if it's not already visible. Select the worksheet, and change the Visibility to xlVeryHidden. Then it can't be unhidden without accessing the VBE.

Tim

I'll have a look when I get home. It'll be easier than faffing about on the phone.

Tim

Right. It won't generate the print preview while the print template worksheet is not visible, so we'll just unhide it to do the print preview, then hide it again afterwards.

See the attached.

Tim

Hi again. Sorry, but I wasn't able to replicate that behaviour; filtering, and re-filtering seemed to work OK, printing completed as expected and the print template was cleared of data ready for the next print.

Please Login or Register  to view this content.

The above clears the data from the print template and resizes the table, but it doesn't touch the totals row at the bottom.

If you're referring to the totals row on the "Invoices" worksheet, I had removed it as I thought you didn't want it to show as this would make it easier to add extra data at the bottom. You can make it visible again by right-clicking anywhere in the table, selecting "Table" then "Totals Row".

Tim

Nah, those lines are already in the code so you don't need to add them.

I still can't replicate the problem, even though I've been round and round filtering, generating print, clearing... doing the same without clearing filters etc.etc.etc... (Just to be clear, I'm running the "test()" sub in module 2.)

I'd suggest you run the macro manually - step by step, using F8. Then you'll be able to see the effect each step is having in the workbook. Remember to make the print template visible to start with and maybe comment out the line that hides it.

Tim

Hi there. Sorry, I've been away for a few days. Glad you got it sorted.

Tim

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.