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
| Public Sub rename()
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\gwanael_kergresse\Desktop\Selction auto\temp.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'settings pour saisie randomisation'
Dim numero As Integer
numero = 101
Set wbk2 = Application.Workbooks.Open("C:\Users\gwanael_kergresse\Desktop\Selction auto\Dossier 2015.xlsm")
Workbooks("temp.xlsm").Activate
Set Rando1 = Range("B1:B300").Find(numero, lookat:=xlWhole)
If Rando1 Is Nothing Then 'si la rando n'est pas renseignée'
MsgBox ("randomisation non définie")
Else 'si elle l'est'
Rando1.Select 'Copie des n° de PE dans le proto'
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
wbk2.Activate
Worksheets("proto").Range("J17").Select
ActiveCell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Workbooks("temp.xlsm").Activate
Rando1.Select 'Copie des n° de modas dans le proto'
Rando1.Offset(0, -1).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
wbk2.Activate
Worksheets("proto").Range("I17").Select
ActiveCell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End If
Workbooks("temp.xlsm").Close
End Sub |
Partager