Bonjour Yochma, le Forum
Voici une procédure qui devrait faire exactement ce que tu veux pour les quatres points mentionnés :
Option Explicit
Const ThePath As String = "C:\Documents and Settings\te\My Documents\TEST\Essai1\[Base.xls]"
Sub WeeklyReport()
Dim TheWeeks() As String
Dim WS As Worksheet
Dim x As Integer, j As Integer, i As Integer, ii As Integer
Dim Tmp1 As String, Tmp2 As String
Dim LastWeek As String
Dim LastNum As Byte
ReDim TheWeeks(Worksheets.Count)
For Each WS In Worksheets
TheWeeks(x) = WS.Name
x = x + 1
Next
For i = LBound(TheWeeks) To UBound(TheWeeks)
For j = LBound(TheWeeks) + ii To UBound(TheWeeks)
If TheWeeks(i) > TheWeeks(j) Then
Tmp1 = TheWeeks(j): Tmp2 = TheWeeks(j)
TheWeeks(j) = TheWeeks(i): TheWeeks(j) = TheWeeks(i)
TheWeeks(i) = Tmp1: TheWeeks(i) = Tmp2
End If
Next j
ii = ii + 1
Next i
LastWeek = TheWeeks(UBound(TheWeeks))
On Error GoTo ErrorHandler
LastNum = CByte(Right(LastWeek, Len(LastWeek) - 7))
On Error GoTo 0
Worksheets(LastWeek).Copy after:=Worksheets(Worksheets.Count)
With Worksheets(Worksheets.Count)
.Name = "Semaine" & LastNum + 1
For i = 2 To 4
.Range("B" & i).Formula = "='" & ThePath & "Sem" & LastNum + 1 & "'!$B" & i
Next
For i = 1 To 7
If Weekday(Date + i) = vbMonday Then .Range("D1") = Date + i
Next
End With
Exit Sub
ErrorHandler:
MsgBox "Le nom de la Feuille " & LastWeek & "n'est pas au format adéquate"
End Sub
Pour la date du lundi suivant de la semaine précédente ... J'ai pas tout compris !! surtout que tu as mis dans ton code "9/23/2004" qui est Jeudi !! lol Donc le code ci-dessus trouveras le prochain Lundi ... A toi de tester et/ou de mieux t'expliquer...
Bon Après Midi
@+Thierry