1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130
| Sub Update()
Dim diese As Range
Dim users As Range
Dim messages As Range
Dim dieseE As Range
Dim usersE As Range
Dim messagesE As Range
Dim dieseR As Range
Dim usersR As Range
Dim messagesR As Range
Dim dieseC As Range
Dim usersC As Range
Dim messagesC As Range
Dim count As Integer
Worksheets("Donnees2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Worksheets("Donnees").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Set diese = Cells.Find(What:="#", After:=Range(Cells(1, 1), Cells(1, 1)), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)
Set users = Cells.Find(What:="Nom d'utilisateur", After:=Range(Cells(1, 1), Cells(1, 1)), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)
Set messages = Cells.Find(What:="Messages", After:=Range(Cells(1, 1), Cells(1, 1)), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)
If (diese.Row = users.Row And users.Row = messages.Row) Then
Set usersE = users.End(xlDown)
' Set usersR = Range(Cells(users.Row, users.Column), Cells(usersR.Row, usersR.Column))
Set messagesE = messages.End(xlDown)
' Set messagesR = Range(Cells(messages.Row, messages.Column), Cells(messagesR.Row, messagesR.Column))
If (messagesE.Row = usersE.Row And messagesE.Row > messages.Row And usersE.Row > users.Row) Then
count = messagesE.Row - messages.Row
Set dieseE = Range(Cells(diese.Row + count, diese.Column), Cells(diese.Row + count, diese.Column))
Set dieseR = Range(Cells(diese.Row, diese.Column), Cells(dieseE.Row, dieseE.Column))
Set usersR = Range(Cells(users.Row, users.Column), Cells(usersE.Row, usersE.Column))
Set messagesR = Range(Cells(messages.Row, messages.Column), Cells(messagesE.Row, messagesE.Column))
dieseR.Copy
Sheets("Membres").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Donnees").Select
usersR.Copy
Sheets("Membres").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("Donnees").Select
messagesR.Copy
Sheets("Membres").Select
Range("C1").Select
ActiveSheet.Paste
Columns("A:C").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Selection.AutoFilter
Selection.AutoFilter
Selection.EntireColumn.AutoFit
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
Worksheets("Membres").Range("A1").Value = "Erreur lors de la mise à jour des donnees"
End If
Else
Worksheets("Membres").Range("A1").Value = "Erreur lors de la mise à jour des donnees"
End If
End Sub |
Partager