I have a book with 32 sheets. One for each day (31 days) and a monthly
roll-up sheet. In my monthly roll-up sheet i would like to have an automatic
"date range" in cell A2.

The date range will always begin with the first day of the month but it will
end with whatever date (sheet) is the last updated. I mean, if I have data on
all my sheets from June 01 to June 25 but June 26 - June 30 are blank (and
the 31st sheet is blank too), the date range should say "June 01 - June 25"
and each day it will get updated when data is entered into a sheet

Does that make sense??

Any ideas will be greatly appreciate it!

Cheers,
Mayte

Change the d2 to a cell you want to check on each sheet. However, I see no
reason not to have all on ONE sheet with a date column and use
data>filter>autofilter to see the individual days. Lots of advantages.

Sub backsardssheet()
For i = Sheets.Count To 2 Step -1
If Sheets(i).Range("d2") > 0 Then
Exit For
End If
Next
MsgBox "June 1 - " & Sheets(i).Name
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Mayte" <(E-Mail Removed)> wrote in message
news82D6152-8AE0-493B-AE9B-(E-Mail Removed)...
>I have a book with 32 sheets. One for each day (31 days) and a monthly
> roll-up sheet. In my monthly roll-up sheet i would like to have an
> automatic
> "date range" in cell A2.
>
> The date range will always begin with the first day of the month but it
> will
> end with whatever date (sheet) is the last updated. I mean, if I have data
> on
> all my sheets from June 01 to June 25 but June 26 - June 30 are blank (and
> the 31st sheet is blank too), the date range should say "June 01 - June
> 25"
> and each day it will get updated when data is entered into a sheet
>
> Does that make sense??
>
> Any ideas will be greatly appreciate it!
>
> Cheers,
> Mayte

Assuming you fire from the Summarysheet.
Sub backsardssheet()
For i = Sheets.Count To 2 Step -1
If Sheets(i).Range("d2") > 0 Then
Exit For
End If
Next
range("a2")= "June 1 - " & Sheets(i).Name
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Change the d2 to a cell you want to check on each sheet. However, I see no
> reason not to have all on ONE sheet with a date column and use
> data>filter>autofilter to see the individual days. Lots of advantages.
>
> Sub backsardssheet()
> For i = Sheets.Count To 2 Step -1
> If Sheets(i).Range("d2") > 0 Then
> Exit For
> End If
> Next
> MsgBox "June 1 - " & Sheets(i).Name
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Mayte" <(E-Mail Removed)> wrote in message
> news82D6152-8AE0-493B-AE9B-(E-Mail Removed)...
>>I have a book with 32 sheets. One for each day (31 days) and a monthly
>> roll-up sheet. In my monthly roll-up sheet i would like to have an
>> automatic
>> "date range" in cell A2.
>>
>> The date range will always begin with the first day of the month but it
>> will
>> end with whatever date (sheet) is the last updated. I mean, if I have
>> data on
>> all my sheets from June 01 to June 25 but June 26 - June 30 are blank
>> (and
>> the 31st sheet is blank too), the date range should say "June 01 - June
>> 25"
>> and each day it will get updated when data is entered into a sheet
>>
>> Does that make sense??
>>
>> Any ideas will be greatly appreciate it!
>>
>> Cheers,
>> Mayte
>

Not sure how to tell if a sheet is empty on purpose or because it has not
been used.
However, this would provide an as of type date, based on the current date.

=TEXT(NOW(), "mmm") & " 01 To " & TEXT(NOW(), "d")


"Mayte" <(E-Mail Removed)> wrote in message
news82D6152-8AE0-493B-AE9B-(E-Mail Removed)...
>I have a book with 32 sheets. One for each day (31 days) and a monthly
> roll-up sheet. In my monthly roll-up sheet i would like to have an
> automatic
> "date range" in cell A2.
>
> The date range will always begin with the first day of the month but it
> will
> end with whatever date (sheet) is the last updated. I mean, if I have data
> on
> all my sheets from June 01 to June 25 but June 26 - June 30 are blank (and
> the 31st sheet is blank too), the date range should say "June 01 - June
> 25"
> and each day it will get updated when data is entered into a sheet
>
> Does that make sense??
>
> Any ideas will be greatly appreciate it!
>
> Cheers,
> Mayte

thank you both !!!

TRY the autofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Mayte" <(E-Mail Removed)> wrote in message
news:198A4F5B-6DB1-46F9-924D-(E-Mail Removed)...
> thank you both !!!