Macro Pivot Table

Microsoft Microsoft office excel 2007 -...
February 26, 2010 at 13:10:29
Specs: Windows 7
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!

See More: Macro Pivot Table

February 26, 2010 at 14:00:48

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.


Report •

February 26, 2010 at 14:11:53
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!

Report •

February 28, 2010 at 05:55:33

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 year

The 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
    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)
    '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
WeekRef = CVErr(xlErrNA)
End Function

Click 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

The function will return "FW 35"
You can include a date directly in the function
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

Report •

Related Solutions

March 16, 2010 at 14:25:34

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.

Report •

March 16, 2010 at 19:18:53

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.


Report •

Ask Question