Worksheet Opens to Current Date of AutoFilter 1

[shelby55]

Hello

I'm using Excel 2003.

I have an Excel document that auto-imports data from an Access database. The document has one worksheet "raw data" that populates the summary chart and charts in the other 3 worksheets. The raw data worksheet has 6 months' of data in it. The summary page has a Filter for all the dates of the raw data and the user selects one to display that date's information. This item is a named range of "userselect".

When the user opens the document I want the AutoFilter on the page that has the dates in it to default to the latest date in the list - how do I do this? Thanks.

 

[SkipVought]

Hi,

Turn on your macro recorder and record setting the AutoFilter for ONE value.

Poast back with your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

HI Skip

I did as requested and get:

Sub test()
Sheets("Summary").Select
Range("D28").Select
End Sub

Thanks.

 

[SkipVought]

Sorry, Shelby, that is NOT your recorded code for setting the AutoFilter.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

What information do you need to help me, Skip? As I told you, I have a worksheet with a pull down cell showing me all the dates within the raw data worksheet. Whatever date is selected then the rest of the worksheet shows metrics from that date.

You suggested it was an autofilter list so I agreed but all I know is it is a pull down list. I didn't create this template, I'm just trying to get it to work as I need it to thus the query about making sure it opens to the last date in the list.

Thanks.

 

[SkipVought]

Well then what kind of dropdown control is it?

AutoFilter?

Data Validation?

MS Forms

ActiveX Control Toolbox?

Each one has its own properties and characteristics?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

I checked with the person who created the template about this field and he said:

The reference date cell F1 is created using data validation with name range. Select cell F1 in summary, click menu Data -> Validation… In the Data Validation dialogue Setting tab, you can see Allow – List and Source = MyDates, “MyDates” is a name range. It is defined as “=OFFSET('Raw Data'!$A$3,0,0,COUNTA('Raw Data'!$A:$A)-2,1)” . You can define name range by selecting menu Insert -> Name -> Define. In the name range definition dialogue, you can find MyDates, or you can define your own.

Does this help?

 

[SkipVought]

Paste this in a MODULE in your VB Project (alt+F11)

sub AssignMaxDate()
   sheets("YourSheetName").Cells(1,"F").value = application.max(Sheets("Raw Data").Range("$A:$A"))
end sub

In the ThisWorkbook object in the VB Project Explorer (ctr+R) paste

Private Sub Workbook_Open()
  AssignMaxDate
End Sub

When your workbook OPENS, the Data Validation list will display the Max Date from Raw Data column A

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Sorry but how do I access a Module to paste that into? Thanks.

 

[shelby55]

Hi Skip

Sorry but I think I found out how to do the first step - right click to view code, select Insert, Module from the menu and paste code.

But I'm not sure about step #2 - thanks.

 

[shelby55]

Hi Skip

Sorry for being a spazz...I figured it out and it works like a charm! You rock!!