We have a large workbook with multiple sheets, one for each project. Each sheet is a billing summary for that project. The sheets are all based on a hidden template sheet that is copied and then filled out and formatted using some VBA and data entered for the project on a User Form. This all works fine.
The problem I'm having is that I'd like to set the template sheet up so that there is a defined print range that doesn't have to be changed every month by the user. When the sheet is created, it is set up for 12 months worth of billing (most projects are done within 6 months, so 12 gives us enough flexibility that I haven't had to add on to the end yet). When we print the sheets for the month, all we are interested in is the last month worth of billing data. We have been doing this by hiding columns and updating the print range each month, but I would like to automate this so the print range does not have to be updated manually each month.
Let me see if this example will help. Pretend that my spreadsheet consists of 9 rows and 26 columns (A through Z). Columns A and B contain equipment information and the date it was installed (and removed, if applicable) and should be printed out every time. Columns C through Z contain monthly data, with 2 columns per month (i.e. month 1 data is in Columns C and D, month 2 data is in Columns E and F, month 3 data is in Columns G and H, and so on until month 12, which is in Columns Y and Z).
After the first month, when it comes time to bill the client, we would want the print range to be from A1 to D9. After the second month, we hide Columns C and D and want the print range to be from A1 to F9 (with columns C and D hidden and, thus, not printing out). After month 3, Columns C through F are hidden and the print range is A1 to H9 (again with columns C through F not printing out). And so on until month 12 when the print range is A1 to Z9 with Columns C through X hidden and not printing out).
I have tried simply setting the print range to be Columns A through D, hoping that when Columns C and D are hidden, Excel would jump to E and F and treat them like C and D, but that doesn't work. It just prints out columns A and B.
I also tried using an offset command to define the print area (I found an article detailing that you can set the print range, which basically defines a named range, then use the Name Manager to enter a formula including an offset to tell Excel how many rows or columns to include). That mostly worked, but there is no way to automatically have Excel know how far out to set the print range (i.e. it doesn't know it is now month 3 unless I set a cell equal to 3 somewhere and read that in to the Offset command).
Does anyone have an idea of how I can make this work without requiring the user to update what month it is and using the offset command I previously tried?
Thanks!
The problem I'm having is that I'd like to set the template sheet up so that there is a defined print range that doesn't have to be changed every month by the user. When the sheet is created, it is set up for 12 months worth of billing (most projects are done within 6 months, so 12 gives us enough flexibility that I haven't had to add on to the end yet). When we print the sheets for the month, all we are interested in is the last month worth of billing data. We have been doing this by hiding columns and updating the print range each month, but I would like to automate this so the print range does not have to be updated manually each month.
Let me see if this example will help. Pretend that my spreadsheet consists of 9 rows and 26 columns (A through Z). Columns A and B contain equipment information and the date it was installed (and removed, if applicable) and should be printed out every time. Columns C through Z contain monthly data, with 2 columns per month (i.e. month 1 data is in Columns C and D, month 2 data is in Columns E and F, month 3 data is in Columns G and H, and so on until month 12, which is in Columns Y and Z).
After the first month, when it comes time to bill the client, we would want the print range to be from A1 to D9. After the second month, we hide Columns C and D and want the print range to be from A1 to F9 (with columns C and D hidden and, thus, not printing out). After month 3, Columns C through F are hidden and the print range is A1 to H9 (again with columns C through F not printing out). And so on until month 12 when the print range is A1 to Z9 with Columns C through X hidden and not printing out).
I have tried simply setting the print range to be Columns A through D, hoping that when Columns C and D are hidden, Excel would jump to E and F and treat them like C and D, but that doesn't work. It just prints out columns A and B.
I also tried using an offset command to define the print area (I found an article detailing that you can set the print range, which basically defines a named range, then use the Name Manager to enter a formula including an offset to tell Excel how many rows or columns to include). That mostly worked, but there is no way to automatically have Excel know how far out to set the print range (i.e. it doesn't know it is now month 3 unless I set a cell equal to 3 somewhere and read that in to the Offset command).
Does anyone have an idea of how I can make this work without requiring the user to update what month it is and using the offset command I previously tried?
Thanks!
