Hi- I'm trying to create a macro for several pivot tables that would select the newest week from the drop down in the report filter when i update the data weekly. For example, If next week is FW34 I would want to select FW34 then the week after FW35, etc. I already created a macro to select FW33 but i know it wont work for next week since its hard coded in. Thanks!

Hi, What is the relationship between FW33/FW34 etc. and the date.

If you can describe the relationship, then it will be possible to create the text "FW33" etc. based on today's date. As today's date is available in VBA, it will just be a matter of getting the first day of this week based on today's date and converting it to FW33/FW34 etc.

Regards

The first day of the week is always a Saturday.

FW 33 is Sat 2/13/10- Fri 2/19/10

FW 34 is Sat 2/20/10- Fri 2/26/10.

Also, would this be possible for Months as well?Thanks for your help!

Hi, You didn't give the full relationship between FW numbers and dates.

From what you have said, I am assuming that FW numbers are based on:

weeks with Saturday as the first day of the week

and FW 1 starts on the first Saturday in July each yearThe first Saturday in July is a variable date - this year (2010) it is 03 July, next year it is 02 July and in 2012 it is 07 July

I have created a User Defined function that you can use as a normal formula. You enter a reference to a cell containing a date and the function returns FW n, where n is the week number.

The function should be correct for any year - it calculates the date for the first Saturday of the year.To use this, open your Workbook.

Click Alt+f11 (The Alt key and function key number 11 at the same time)

This opens the Visual Basic Window.

In the Project Explorer window (usually on left), find 'VBAProject(YourWorkbook.xls)'

Right click on it and select 'Insert' then 'Module' (not Class Module)

Double click Module1 which is under the Modules folder

Enter the following code in the main window.Option Explicit Public Function WeekRef(dtWeek As Date) As Variant Dim dtStartOfWk Dim dtFirstSat As Date Dim intBaseYear As Integer Dim dtFirstJuly As Date Dim dtBase As Date Dim intWkNum As Integer Dim n As Integer On Error GoTo ErrHnd 'get start of this week If Weekday(dtWeek) = 7 Then dtStartOfWk = dtWeek Else dtStartOfWk = dtWeek - Weekday(dtWeek) End If 'get first Saturday in this July For n = 0 To 6 If Weekday(DateSerial(Year(dtWeek), 7, 1) + n) = 7 Then dtFirstSat = DateSerial(Year(dtWeek), 7, 1) + n End If Next n 'get year - this year if after 1st Saturday in July - else last 'test if this date is On or after first Saturday in July If dtWeek >= dtFirstSat Then 'starting point is this year intBaseYear = Year(dtWeek) Else 'starting point is last year intBaseYear = Year(dtWeek) - 1 End If 'get 1st Saturday of the base year dtFirstJuly = DateSerial(intBaseYear, 7, 1) 'test first seven days for Saturday For n = 0 To 6 If Weekday(dtFirstJuly + n) = 7 Then dtBase = dtFirstJuly + n End If Next n 'calculate difference between start of this week and base date 'divide by 7 to get week number -1, so add 1 intWkNum = ((dtStartOfWk - dtBase) / 7) + 1 'create text WeekRef = "FW " & CStr(intWkNum) Exit Function 'error handler ErrHnd: WeekRef = CVErr(xlErrNA) End FunctionClick File-Save from the VB Menu

Use Alt+f11 to return to the main Excel window

Enter a date in say cell A1. If cell A1 contains "2-Mar-10"

Enter this in any other cell=weekref(A1)The function will return "FW 35"

You can include a date directly in the function=weekref("14/Oct/10")The function returns "FW 15"

You can place any calculation which returns a valid Excel-recognized date inside the weekref function.You asked

would this be possible for Months as well?

I would need more information to be able to do something along the same lines.

What code is returned - is it FW 1 to FW 12 or FM 1 to FM12 and is it just Calendar months.

Also what is the first month of the year - January or July

Regards

Hi- Thanks, I see how you can assign a FW to a date, but im not sure if that will help with pivot tables.

Im trying to create a macro to select the next FW from the drop down/report filter for several pivot tables.

Thanks for your help.

Hi, I am not sure what you are looking for.

I offered a function that would allow you to calculate FWs, so that you would be able to know both the current FW and the next FW.

If you know the FW's then your macro can use them to place these values in the appropriate places in the pivot tables.

Perhaps posting your macro would help, and telling us what is not working and what happens when you run it.

Regards

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History