Master broken into week 1, week2, etc...

I have an Access database and I'm going to have the user select a month from a dropdown on a form in the database, have a query create a new table based on that criteria, then a macro will call on a spreadsheet, which will have a database query to the newly created table... the table/master sheet will have a list of order numbers and dates and other data....

what I don't know how to do and need some direction is I need that master sheet to then be broken into 5 other sheets when the workbook is opened... sorting the data into Week 1, Week 2, Week 3, Week 4 and Week 5.

Can someone help me out or point me in the right direction?

Thank you in advance to anyone who replies :D

Hi :hi:

This function may be of help.
I assumed that 1st day of the week is Monday, you may want to change that. Also, in the line highlighted in red, you might have to change the whole expression, because of regional settings. (I'm not sure about the latter.)
[vba]Function WhichWeek(Cell As Range) As Long
Dim Ref As Date, RefWeekDay As Long

Ref = CDate(Year(Cell) & "." & Month(Cell) & ".1")
RefWeekDay = Weekday(Ref, vbMonday)

WhichWeek = (Day(Cell) + RefWeekDay - 2) \ 7 + 1
End Function
[/vba]
By the way, a month can span over 6 weeks even, like this July, so count on 6 new sheets instead of 5.
See the attached example.

Jimmy

sweet! :D this will definitely help... thank you... i'm still not sure how i'm going to do this, but at least i have a function with which I can test if my data meets the criteria... thank you.

One possible way is
[vba]
Function WhichWeek(Cell As Range) As Long
Dim Ref As Date, RefWeekDay As Long

Ref = CDate(Year(Cell) & "." & Month(Cell) & ".1")
RefWeekDay = Weekday(Ref, vbMonday)

WhichWeek = (Day(Cell) + RefWeekDay - 2) \ 7 + 1
End Function

Sub SortByWeek()
Dim MS As Worksheet, DS As Worksheet
Dim MR As Range, DR As Range, c As Range
Dim WeekNo As Long

'defining Master Sheet and Master Range
Set MS = ThisWorkbook.Sheets("Master")
Set MR = MS.Range("A1", MS.Range("A" & MS.Rows.Count).End(xlUp))

'looping through Master Range (cells that contain dates)
For Each c In MR.Cells
'obtaining week number
WeekNo = WhichWeek(c)

On Error GoTo CreateSheet
'defining destination sheet, if it doesn't exist, create it
Set DS = ThisWorkbook.Sheets("Week" & WeekNo)
'defining destination range
Set DR = DS.Range("A" & DS.Rows.Count).End(xlUp).Offset(1)
'copying a row from Master Sheet to Destination Sheet
c.EntireRow.Copy DR
Next

Exit Sub

CreateSheet:
ThisWorkbook.Worksheets.Add
Set DS = ActiveSheet
DS.Name = "Week" & WeekNo
Resume Next
End Sub
[/vba] I am assuming that the sheet with your data is called "Master", and that dates you want to sort by are located in column "A". Without knowing the layout of your table/sheet, I can not do much more. :dunno

Jimmy

Works exactly as I want it to... there are a few things I need to change to accomodate my specific needs, but this is absolutely what I was looking for.
Consider this one solved!!! :D

Thank you for both your answers! I learned a lot!

I'm wondering tho, in this line:

Ref = CDate(Year(Cell) & "." & Month(Cell) & ".1")

I changed this so that it would work for me... I just used (Cell)... but I'm wondering why your code reads liket his... is this some conversion that you need to do in Europe or something?

Okay, you asked for it.

Yeah, the rest of the world has to coerce dates because stupid MS and stupid VBA decided that even though the majority of the world doesn't use US style dates, and even though we have regional settings on our computers that can tell each application what settings we use, including NORMAL date settings, MS and VBA know best and inflict US style dates in VBA regardless.

^^ thank you for that explanation and sorry for MS's arrogance :/



the code is getting confused at this point:

WhichWeek = (Day(Cell) + RefWeekDay - 2) / 7 + 1

it's placing July 5th in Week 2... I'm trying to break it down and understand how this formula is supposed to work, but I'm havin' troubles with it... can you explain to me?

The Day of (Cell), which is 5, plus RefWeekDay, which is 5 = 10, minus 2 makes 8, divided by 7 makes 1, plus 1 means Week 2... If I were to take out the " + 1" it would give me what I want, but I don't know if that would work in all cases... and the whole formula just feels strange to me... I'm probably not understanding the formula enough for it to be that easy .

It all comes down to what you mean by "week".
For me, a week starts with Monday and ends with Sunday, so July 1st, which is Sunday, is the only day in 1st week of July. 2nd of July is Monday, it belongs to the next week, which is the 2nd week of July. See it all (kind of) charted below.

Code:

---

July, 2007.     
            Mo  Tue  Wed  Thu  Fri  Sat  Sun     
Week 1                                          1
Week 2      2    3    4    5    6    7    8     
Week 3      9    10    11    12    13    14    15     
Week 4    16    17    18    19    20    21    22     
Week 5    23    24    25    26    27    28    29     
Week 6    30    31   

---

So what's wrong with placing July 5th in Week 2? How should it be, in your opinion?

Jimmy

It's not / but \. You want a whole number.

@jimmythehand: ah... i see... I'm in the states, so our weeks start on Sunday... but I did change it to vbSunday to accomodate this...

I've changed a few other things to accomodate all of my particular needs, but none of the things I've changed should cause it to react as such... i've attached a simplified copy of the workbook to this post.

Normally, the worksheet is filled with formulas that are linked to another sheet that is a query to a table in an access database. All of those things have been removed from the version I've uploaded, but, as expected, the code still acts in the same manner. Can you take a look?

@Charlize: wow... so that's what that does... that's going to come in hand :D thanks for clearing that up for me!

I don't think that you properly implemented Jimmy's Whichweek function

[vba]

Function WhichWeek(Cell As Range) As Long
Dim Ref As Date, RefWeekDay As Long

Ref = CDate(Year(Cell.Value) & "/" & Month(Cell.Value) & "/01")
RefWeekDay = Weekday(Ref, vbSunday)

WhichWeek = (Day(Cell.Value) + RefWeekDay - 2) \ 7 + 1
End Function
[/vba]

Okay, I will try to explain, meanwhile changing my algorithm to US system.
See the attached workbook for reference.

Let's assume that the current month begins with Sunday, Week #1. This assumption holds for July, so it's a perfect month for us right now.
If I want to calculate which week a day is in, I need to use something like this formula:

Code:

---

Week = INT(DayOfMonth /7)

---

This is shown in column E. You can see that the 7th day belongs to the 2nd week, which is wrong, and is corrected in column F with the following formula:

Code:

---

Week = INT((DayOfMonth-1) /7)

---

Compare the yellow cells.
Now, if I want the 1st week to get an index of 1 instead zero, I have to add 1 to the result. See column G, and the following formula

Code:

---

Week = INT((DayOfMonth-1) /7)+1

---

This is, so far, all happy and works well, but once we reach August, it will give wrong results. See red cells in column G.
5th Aug, which is Sunday, should belong to Week 2, instead of Week 1.
This happens because August doesn't start with Sunday.
August starts with Wednesday, which is 4th day of the week, so it is, sort of, a 3 days offset to 1st day of the week (Sunday). This means, the whole month is shifted by 3 days. This is corrected in column H:

Code:

---

Week = INT((DayOfMonth-1+3) /7)+1

---

But column H is only valid for August, as September starts with Saturday, which is a 6 days offset. So, in order to get a formula that is valid for all and each months, you need a reference day in each month. The most simple choice is 1st day of the month.
So the formula is:

Code:

---

Week = INT((DayOfMonth-1 + Weekday(1st day of month) -1) /7)+1
'which is the same as
Week = INT((DayOfMonth + Weekday(1st day of month) -2) /7)+1

---

And it was here that I noticed Bob's post, and decided that the rest should be self-evident. (With some thinking, probably.)

Jimmy

Not sure if this will work in your formulas.
Try Weeknum()
it is part of the add-in for Analysis ToolPak

I use it where there is a date in column A,
Column A is a named range "jobdate"
and another column in same row put =weeknum(A1) and fill down
' this is a helper column
this helper row is named wknum

in another formula I refernece the result with sumproduct

=sumproduct(--(data1=value),--(data2=value2))

my actual code looks like this:

=SUMPRODUCT(--(Pass="x"),--(JobDate=WEEKNUM(WkStart)))

pass, jobdate and wkstart are named ranges

data1 is the range you are looking in
value 1 is what you limit the results by
if you have dats in column A from jan to aug
- the valu could be any month that falls in that range...
The true result will then compare the second criteria

Or you can just use sumproduct without the helper column
You have to name a cell as StartDate and EndDate
set up your sheet with columns where you enter a date,
- and name the range "JobDate"

in a cell you want the sum or result type:
=sumproduct(--(JobDate=>startdate),--(jobdate=<stopdate),--(check=value))

I have a few posts with this sumproduct and weeknum()
look up under mperrah
lucas, mdmcillop and charlize have all helped with this as well.
Mark