none
Automatically rolling print area RRS feed

  • Question

  • I have a chart that shows machine utilization week to week for multiple years. I have conditional formatting that highlights the boxes above the data for dates that fall between the current date and 6 months from the current date. Every week, the highlighting will roll to the next week and 6 months past that. I am wondering if there is a way that I can set it up so that the print area is always around the data that lies under the highlighted boxes. I need the print area to roll automatically without manually adjusting it every week. Is this possible? How could I accomplish it? If a macro is needed, what would it look like?
    Thursday, June 23, 2016 6:17 PM

Answers

  • I'd approach this by adding filters to the data set (which should be formatted as a table to keep things easy).

    Record a macro that sets the date filter to 'between' then modify the macro to change the manually derived dates you selected into computed dates (today and today + 6 months). Then add a button above the data that, when clicked, fires off the macro

    I'd also have a macro to clear the filter just to make it easy to see all the data.

    I do something similar with a time logging app so, after I put in one line for hours 'today', I run a macro to just display today's data. That macro looks like:

    Sub FilterToday()
    '
    ' FilterToday Macro
    '

    '
        Sheets("TimeInputTable").Select
        ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _
            xlFilterToday, Operator:=xlFilterDynamic
      
    End Sub

    Friday, June 24, 2016 3:52 PM

All replies

  • Hi SamGK,

    can you show us your conditional formatting and the output of high lighting.

    Are you only using conditional formatting or you have some VBA code.

    if we can see the snap shot then we can get some idea about requirement because it is something unclear.

    I am also checking if we can achieve this using user interface if I  find something then you not need to create macro.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 24, 2016 2:43 AM
    Moderator
  • I'd approach this by adding filters to the data set (which should be formatted as a table to keep things easy).

    Record a macro that sets the date filter to 'between' then modify the macro to change the manually derived dates you selected into computed dates (today and today + 6 months). Then add a button above the data that, when clicked, fires off the macro

    I'd also have a macro to clear the filter just to make it easy to see all the data.

    I do something similar with a time logging app so, after I put in one line for hours 'today', I run a macro to just display today's data. That macro looks like:

    Sub FilterToday()
    '
    ' FilterToday Macro
    '

    '
        Sheets("TimeInputTable").Select
        ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _
            xlFilterToday, Operator:=xlFilterDynamic
      
    End Sub

    Friday, June 24, 2016 3:52 PM
  • Hi SamGK,

    since you created this thread you did not reply to the suggestions given by the community members.

    did your issue solved? if so would you like to share the suggestion with our community so that if some other member have same issue like you can also get solution by your post.

    if your issue is still exist then please let us know so that we can try to provide you further suggestions.

    one another idea is that after you apply conditional formatting then try to find Highlights in the sheet and try to print that page. you can try to find highlights using colors you had use. like Red color.

    so when it find you need to print that page and this is how you can try to roll your print area.

    try to implement this logic in your code and if you stuck somewhere we will again try to help you.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 27, 2016 7:07 AM
    Moderator