Dynamic print area based on date

[Astrid123]

Hello all,

In my Excel I have a range of dates. This calendar consists of quite some cells. What I want is to automatically set the print area based on today's date including the range of two days before and two days after and always print the first column.

For example if this is my sheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]20-8[/TD]
[TD]21-8[/TD]
[TD]22-8[/TD]
[TD]23-8[/TD]
[TD]24-8[/TD]
[TD]25-8[/TD]
[TD]26-8[/TD]
[TD]27-8[/TD]
[TD]28-8[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to automatically print this (the current date +2 days before and after, and the first column)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]22-8[/TD]
[TD]23-8[/TD]
[TD]24-8[/TD]
[TD]25-8[/TD]
[TD]26-8[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Does someone know how to do this?

Cheers!

 

[James006]

Hello,

If you are using actual dates (and not Text ...) you should be able to use the Match function to locate your Today's date ... and resize your range accordingly ...

Say you would like when opening your file ... to get the cursor to jump directly to Today's date ...

Private Sub Workbook_Open()
' Macro to jump to Today's Date
Dim dateRng As Range
Dim DateCel As Range
Dim dateStr As String
  Worksheets("Sheet1").Select
  Set dateRng = Range("1:1")
    For Each DateCel In dateRng
        DateCel.Activate
        ActiveCell.Select
        On Error Resume Next
        dateStr = DateCel.Value
        If dateStr = Date Then
            DateCel.Select: Exit Sub
        End If
    Next DateCel
End Sub

Hope this will help

 

[Astrid123]

Hello James,

Thank you for your answer, even though it's seems to be a very useful macro, it's not really what I've been looking for.

What I want the Excel to do is to automatically adjust the print area instead of jumping to today's date. I read that it is possible to do with the use of the name manager. But I don't know exactly how to manage this.

You have any suggestion?

 

[James006]

Hello,

Since I do not have your worksheet in front of me ...

Could you test the following formula : (as long as your dates are located in Row 2 ...)

=MATCH(TODAY(),2:2,0)

And let me know if you are getting a result ...

 

[James006]

As far as using the name manager ...

You could give a name to your range ... say myprint ...

and define it using the offset formula ... such as

=OFFSET(Sheet1!Z1,0,-2,8,5)

in this formula Sheet1!Z1 is he result of the previous Match formula ... needed to locate Today's date ...:wink:

HTH

 

[James006]

Re,

To expand on my previous message ...regarding your Advanced Dynamic Named Range ... :wink:

[TABLE="width: 28"]
<tbody>[TR]
[TD="class: xl63, width: 28"]=OFFSET(INDIRECT("Sheet1!"&ADDRESS(1,MATCH(TODAY(),Sheet1!$2:$2,0),4)),0,-2,8,5)[/TD]
[/TR]
</tbody>[/TABLE]

This formula is based on the following assumptions :

1. You are using Actual dates ... and not text ...
2. Your worksheet tab name is Sheet1
3. Your dates are located in row 2
4. You need to always have a 5 Column wide range
5. Your range has 8 rows

Let me know if you need further explanations ...

HTH