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
| Private Sub Valider_Click()
Dim MaSelection As Range
Dim wb_destination As Workbook
Dim ws_destination As Worksheet
Set wb_destination = Workbooks("MC_essai.xlsm")
Set ws_destination = wb_destination.Worksheets("Saisie")
Dim Wb_source As Workbook
Dim Ws_source As Worksheet
Dim ZoneColle As Range
' Contrôle de la saisie
' Récupération des données dans classeur fermé que j'ouvre !!
Workbooks("MC_essai.xlsm").Worksheets("Saisie").Cells.ClearContents
' Premier atelier
Set Wb_source = Workbooks.Open(Filename:="\\Gpao\commun\30_QUALITE\\307_Gestion_de_service\Main_courante_atelier\MC_Plastique.xlsm")
Set Ws_source = Wb_source.Worksheets("Synthese")
Ws_source.ListObjects("Tableau1").Range.AutoFilter Field:=2, Criteria1:= _
N°Semaine.Text
Set MaSelection = Ws_source.Range(Range("A5"), Cells(Range("B65536").End(xlUp).Row, 19))
' MaSelection.Select
MaSelection.SpecialCells(xlCellTypeVisible).Copy _
ws_destination.Range("A5")
Wb_source.Close False
' zonecolle fait référence à Workbooks("MC_essai.xlsm").Worksheets("Saisie")
Set ZoneColle = ws_destination.Range("A65536").End(xlUp).Offset(1, 0)
Set Wb_source = Workbooks.Open(Filename:="\\Gpao\commun\30_QUALITE\\307_Gestion_de_service\Main_courante_atelier\MC_Expédition.xlsm")
Set Ws_source = Wb_source.Worksheets("Synthese")
Ws_source.ListObjects("Tableau1").Range.AutoFilter Field:=2, Criteria1:= _
N°Semaine.Text
Set MaSelection = Ws_source.Range(Range("A5"), Cells(Range("B65536").End(xlUp).Row, 19))
' MaSelection.Select
MaSelection.SpecialCells(xlCellTypeVisible).Copy _
ZoneColle
Wb_source.Close False
' Prochain Atelier
Set ZoneColle = ws_destination.Range("a65536").End(xlUp).Offset(1, 0)
Set Wb_source = Workbooks.Open(Filename:="\\Gpao\commun\30_QUALITE\\307_Gestion_de_service\Main_courante_atelier\MC_Finition.xlsm")
Set Ws_source = Wb_source.Worksheets("Synthese")
Ws_source.ListObjects("Tableau1").Range.AutoFilter Field:=2, Criteria1:= _
N°Semaine.Text
Set MaSelection = Ws_source.Range(Range("A5"), Cells(Range("B65536").End(xlUp).Row, 19))
MaSelection.Select
MaSelection.SpecialCells(xlCellTypeVisible).Copy _
ZoneColle
Wb_source.Close False
Set ZoneColle = ws_destination.Range("a65536").End(xlUp).Offset(1, 0)
Set Wb_source = Workbooks.Open(Filename:="\\Gpao\commun\30_QUALITE\\307_Gestion_de_service\Main_courante_atelier\MC_Luxe.xlsm")
Set Ws_source = Wb_source.Worksheets("Synthese")
Ws_source.ListObjects("Tableau1").Range.AutoFilter Field:=2, Criteria1:= _
N°Semaine.Text
Set MaSelection = Ws_source.Range(Range("A5"), Cells(Range("B65536").End(xlUp).Row, 19))
MaSelection.Select
MaSelection.SpecialCells(xlCellTypeVisible).Copy _
ZoneColle
Wb_source.Close False
UserForm1.Hide
End Sub |
