Select Cells based on Todays date and print selection 1

[liamm]

Hi on my worksheet I have 6 columns. The first column is a date 'ddd-dd-mm', the next 5 are general text. Each Date cell is a merge of 3 rows (example: A2 covers Rows 2,3,4 and so on). So for example today covers the cells A2:F4.

I would like to create a macro (activated by a button) which (based on todays date) selects the next 7 days and prints only the associated cells. For example if today starts at A2(and covers 3 rows)tomorrow is A5 etc, I want to select cells A2:F22 and Print the selection.

Hope this is clear
Liam

If you can help, GREAT
If I can help, EVEN BETTER

 

[SkipVought]

Hi,

What work have you done on this taks? Please post the code that you have so far.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[liamm]

Also, if possible can the worksheet be automated so when it opens it places the Row with todays date at the top of the screen?
Liam

If you can help, GREAT
If I can help, EVEN BETTER

 

[liamm]

Hi SkipVought,
all I have managed so far is to focus on Todays date but this only focuses on the cell, it doesn't put that Row at the top of the page.
I also recorded a macro to print a selection of cells but of course this only selcted the same cells each time. I didn't know where to start with moving the cells down each day

Liam

If you can help, GREAT
If I can help, EVEN BETTER

 

[SkipVought]

Why not use the AutoFilter to select dates >= Today AND < Today + 5

Turn on your macro recorder. Post your code.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[liamm]

Hi, I tried this and it removed all fields.
The code is:

Sub Macro2()
Selection.AutoFilter Field:=1, Criteria1:=">=TODAY", Operator:=xlAnd, _
Criteria2:="<TODAY+5"
End Sub

Do I have to tell it the date format? and if yes , how would I do this
Liam

If you can help, GREAT
If I can help, EVEN BETTER

 

[SkipVought]

You have to use TODAY's DATE!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[liamm]

Can I not use something like Now()? instead of having to type in the date each time

If you can help, GREAT
If I can help, EVEN BETTER

 

[SkipVought]

Post your recorded code. Variables do not work using Excel features. That's why you are recording code so that we can get it to work with variables like Date or Now.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[liamm]

Hi, when I use a date it filters:

Sub Macro1()
Selection.AutoFilter Field:=1, Criteria1:=">=18/01/2009", Operator:=xlAnd _
, Criteria2:="<25/01/2009"
End Sub

Can I do this without having to type in the date?

If you can help, GREAT
If I can help, EVEN BETTER

 

[SkipVought]

Assuming that your table starts in A1...

Sub Macro1()
    [A1].AutoFilter Field:=1, _
       Criteria1:=">=" & format(date,"dd/mm/yyyy"), Operator:=xlAnd, _
       Criteria2:="<" & format(date+5,"dd/mm/yyyy")"
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[liamm]

Sorry, this didn't work. I got a compile error showing the last " mark at the end of Criteria 2 line.

When I removed this " the macro ran but the filter showed no fields.

If you can help, GREAT
If I can help, EVEN BETTER

 

[SkipVought]

Sub Macro1()
    [A1].AutoFilter Field:=1, _
       Criteria1:=">=" & format(date,"dd/mm/yyyy"), Operator:=xlAnd, _
       Criteria2:="<" & format(date+5,"dd/mm/yyyy")
End Sub

sorry, it had a trailing QUOTE in criteria2.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[liamm]

Sorry, this did not work It filtered out all the results so that it showed no data at all.
Can I ask, in the coding how does it get todays date from the code?

Thanks for all this help,
Liam

If you can help, GREAT
If I can help, EVEN BETTER

 

[SkipVought]

Hmmm???

Works on my sheet. Since my regional settings (mm/dd/yyyy format) are probably different than yours, here's my code...

Sub Macro11()
    [A1].AutoFilter Field:=1, _
       Criteria1:=">=" & Format(Date, "mm/dd/yyyy"), Operator:=xlAnd, _
       Criteria2:="<" & Format(Date + 4, "mm/dd/yyyy")
End Sub

The current date in VB is Date.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Are you using REAL DATES? faq68-5827.

By changing the Cell Format in column A to GENERAL, it should display NUMBERS; for instance today's date serial number is 39831.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[liamm]

Hi again, tried your last code and it worked - mm/dd/yyyy.
I have formatted my cells ddd-dd-mmm and used your code and the script works smashing.
I just need to get my head around how I want the spreadsheet to work now. i.e. I would like it to Open with todays cell at the top of the page and when I run the code you have created I would like it to print the selection as part of the same code. For example the button could say "Print next 7 days" and the code would filter the data then open the Print info box so the user just needs to select the printer (or even print the sheet automatically)

Liam


If you can help, GREAT
If I can help, EVEN BETTER