Issues trying to dynamically establish the print range
Hi Experts,
I'm having several issues trying to create a dynamic print range for Sheet1 which has 8 side-by-side(contiguous) Tables. While I am encountering several issues in the process, they may be related but if at the end of the day, it turns out that I need to add additional points (via "pointer questions") because I've raised more than one issue, I'd be happy to do that - I just don't want to have to break this up into separate issues because I don't know if these issues are directly related or not - I can only say that they are all frustrating my purpose here.
I'd like the Print Area to be restricted to the visible columns in the selected Table (there are 8 macro buttons in Column A that display the 8 Tables while hiding all of the other columns not in the given Table. While the Sheet has formulas and conditional formatting down to Row 515, I only want to print those rows whose formulas return data. Also, Column A should not be printed with any of the Tables. I'm not married to the method I am using (and am willing to go a different direction) but I am still interested in what is wrong with this approach.
These are the issues I am seeing:
1) I've tried to use the advice at: http://stackoverflow.com/q
... and have set up a dynamic range ("MyNamedRange") in Excel with the following formula:
=OFFSET(Sheet1!$B$15, 0, 0,COUNTA(Sheet1!B15:B515),SUBTOTAL(103,Sheet1!15:15))
But, after running the macros to select the Table and viewing the Print Preview, and then examining the Range "MyNamedRange" (note there are also Worksheet change events code that runs, too but it should be unrelated to this print area setting macro), I see the formula changed to:
=OFFSET(Sheet1!$B$15, 0, 0,COUNTA(Sheet1!XEZ17:XEZ517),SUBTOTAL(103,Sheet1!17:17))
I don't know why - I tried it first as a Workbook level named range, but it is presently set at a Worksheet level range, but neither seems to make any difference to the anomalies I am seeing here. Why is this happening?
2) The SUBTOTAL formula with the 103 parameter is supposed to ignore hidden values but with only 7 visible columns (in it's present state (after invoking the View_Client macro (with the Add/Delete Client button), this portion of the formula is returning a count of 33 (and checking the count of items in row 15 after clicking the "View All Columns" button, I find that there are 33 items with data on row 15). BTW, I'm testing that by using the formula in Cell G1 and selecting just the Subtotal function and pressing F9 to Calculate just that component but then escaping out so the calculated result doesn't replace the formula (I do realize that the formula in a worksheet doesn't work the same as it does in a dynamically named range (thus the #VALUE error in G1), so I am just doing that for testing purposes. Why am I getting the wrong result with this, or maybe more specifically, why are the Hidden columns being counted?
3) When I first started running this code (after manually creating the dynamic "MyNamedRange"), the code ran without error. Now, I am getting a Run-time error 1004 "Reference is not valid" on the 2nd line of code below. Somehow (and I don't know how), after getting this error repeatedly, the macro intermittently was working, but now it keeps on stopping with that error. Why is this happening?
4) After clicking the Add/Delete Client, Arrivals or Departure buttons and looking at Print Preview, the resulting display is (somehow, without running the "SetMyPrintArea" macro) exactly the range I want. However, if I click on the Car Groups, Avis Porsche Matrix, Room Requirements, or Special Request buttons, the resulting Print Preview is showing a blank page. Since all of these macros do the same thing with the only difference being which columns are hidden and which are displayed (based upon a key word in Row 600), I don't know why some of them come up blank (Edit: There is one difference in the View_Room_Requirements_V1"
ThisWorkbook.Sheets(1).Ran
- it is True for the other 7 macros, and False in this one as I want those rows displayed only in the related Table). What explains these blank pages and how can I fix it?
Sorry for the long post, but these are all issues I'm encountering in trying to get this to work.
Hope someone can assist here!
Jeff
EE-Example.xlsm
I'm having several issues trying to create a dynamic print range for Sheet1 which has 8 side-by-side(contiguous) Tables. While I am encountering several issues in the process, they may be related but if at the end of the day, it turns out that I need to add additional points (via "pointer questions") because I've raised more than one issue, I'd be happy to do that - I just don't want to have to break this up into separate issues because I don't know if these issues are directly related or not - I can only say that they are all frustrating my purpose here.
I'd like the Print Area to be restricted to the visible columns in the selected Table (there are 8 macro buttons in Column A that display the 8 Tables while hiding all of the other columns not in the given Table. While the Sheet has formulas and conditional formatting down to Row 515, I only want to print those rows whose formulas return data. Also, Column A should not be printed with any of the Tables. I'm not married to the method I am using (and am willing to go a different direction) but I am still interested in what is wrong with this approach.
These are the issues I am seeing:
1) I've tried to use the advice at: http://stackoverflow.com/q
... and have set up a dynamic range ("MyNamedRange") in Excel with the following formula:
=OFFSET(Sheet1!$B$15, 0, 0,COUNTA(Sheet1!B15:B515),SUBTOTAL(103,Sheet1!15:15))
But, after running the macros to select the Table and viewing the Print Preview, and then examining the Range "MyNamedRange" (note there are also Worksheet change events code that runs, too but it should be unrelated to this print area setting macro), I see the formula changed to:
=OFFSET(Sheet1!$B$15, 0, 0,COUNTA(Sheet1!XEZ17:XEZ517),SUBTOTAL(103,Sheet1!17:17))
I don't know why - I tried it first as a Workbook level named range, but it is presently set at a Worksheet level range, but neither seems to make any difference to the anomalies I am seeing here. Why is this happening?
2) The SUBTOTAL formula with the 103 parameter is supposed to ignore hidden values but with only 7 visible columns (in it's present state (after invoking the View_Client macro (with the Add/Delete Client button), this portion of the formula is returning a count of 33 (and checking the count of items in row 15 after clicking the "View All Columns" button, I find that there are 33 items with data on row 15). BTW, I'm testing that by using the formula in Cell G1 and selecting just the Subtotal function and pressing F9 to Calculate just that component but then escaping out so the calculated result doesn't replace the formula (I do realize that the formula in a worksheet doesn't work the same as it does in a dynamically named range (thus the #VALUE error in G1), so I am just doing that for testing purposes. Why am I getting the wrong result with this, or maybe more specifically, why are the Hidden columns being counted?
3) When I first started running this code (after manually creating the dynamic "MyNamedRange"), the code ran without error. Now, I am getting a Run-time error 1004 "Reference is not valid" on the 2nd line of code below. Somehow (and I don't know how), after getting this error repeatedly, the macro intermittently was working, but now it keeps on stopping with that error. Why is this happening?
Sub SetMyPrintArea()
Sheets(1).Activate
ActiveSheet.PageSetup.PrintArea = "MyNamedRange"
End Sub4) After clicking the Add/Delete Client, Arrivals or Departure buttons and looking at Print Preview, the resulting display is (somehow, without running the "SetMyPrintArea" macro) exactly the range I want. However, if I click on the Car Groups, Avis Porsche Matrix, Room Requirements, or Special Request buttons, the resulting Print Preview is showing a blank page. Since all of these macros do the same thing with the only difference being which columns are hidden and which are displayed (based upon a key word in Row 600), I don't know why some of them come up blank (Edit: There is one difference in the View_Room_Requirements_V1"
ThisWorkbook.Sheets(1).Ran
- it is True for the other 7 macros, and False in this one as I want those rows displayed only in the related Table). What explains these blank pages and how can I fix it?
Sorry for the long post, but these are all issues I'm encountering in trying to get this to work.
Hope someone can assist here!
Jeff
EE-Example.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER