Set print Area

[barpat96]

Is it possible for a macro to set the print area based on cells that have data? Here is what i am wanting to print.
A1:G1000 but only if Data is present (each row has hidden formulas) and I want Row 2 to repeat at the top of every page. I tried this with the Set print area but i get a lot of blank pages with only row 2


not sure if it matters but Columns H-N have data that i do not want printed

Thanks

[Simon Lloyd]

perhaps something like this:

Please Login or Register  to view this content.

everytime you change a worksheet the print area will change, however the range will depend on data in column G so if you have 100 rows of data in column A and only 10 in G then the print area will be A1:G10, if you swap the A and the G around in the code then your print area would be A1:G100.

[shg]

Perhaps in the Workbook_BeforePrint event rather than the Worksheet_Change event?

[barpat96]

Thanks for your responses. I tried both and it doesnt seem to work. Still prints 25+ pages. Any suggestions?

[shg]

Would you please post a sample workbook that illustrates the problem, and describe your desired result?

[barpat96]

Blank - 2008.zip

Not sure if this is what you wanted or not. The desired result would be that if only 32 rows have data in them then only print 32 Rows, not 25 pages of blank paper with Row 2 being repeated on each page. Data is added daily until the end of the month so the list gets bigger each day.

Thaks Again

[Simon Lloyd]

It doesnt work because you have pasted the code in to a standard module (module 8) it needs to be pasted in to the "ThisWorkbbok" module!
you need the code to look like this:

Please Login or Register  to view this content.

[barpat96]

thank you Simon,
I pasted it first in the Thisworkbook module of the Personal.xls file so it can be used in other workbooks. When it didnt work i tried it in the file itself. Let me try again

[barpat96]

Should this effect show in print preview? does the macro run Automatically or do i need to create a button?

[Simon Lloyd]

it runs automatically everytime you change something on any worksheet.

Take a look at the attached, add and remove data to see the effect, remember though...setting the print area will not set your margins, you have to do that in page setup.

[barpat96]

Thanks simon,
That does work perfectly for what i want. but when i put in effect for my workbook it doesnt do anything. I got to looking at the thisworkbook module and i have the following code already in there to save the workbook after input of data. Could this be affecting it?

Please Login or Register  to view this content.

[barpat96]

I did some research and learned that you cant have 2 private sub Workbook sheet change or you will get an Ambigous Name detected error. So i guess i need to make these 2 codes in one. I Tried but it doesnt seem to work. I am still learning this so any help here is appreciated. I will post the 2 codes and maybe someone can help make them in one
Thanks

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[barpat96]

im still trying to get theses two codes to work in the same workbook. No luck yet

[barpat96]

I finally Got it figured out. Thanks Simon and SHG for your help.

[Simon Lloyd]

Please Login or Register  to view this content.

try this, the sub that you have in the ThisWorkBook module called Sub StopAllEvents() will not work, but i have incorporated what you want in the code above!