Aide VBA - Décaler collage
T
Bonjour à tous,
Je compile des fichiers grâce au code de Monsieur Steelson, mais je souhaiterais décaler ce collage de manière à insérer en colonne A une autre colonne. De fait, simplement décaler le début du collage en colonne B!
Ci joint le code :
Option Explicit
Sub collecter()
Dim wbk1 As Workbook, wbk2 As Workbook, ws1 As Worksheet
Dim MonRepertoire, Repertoire As FileDialog, monFichier$, derL%, derL1%
Set Repertoire = Application.FileDialog(msoFileDialogFolderPicker)
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choix du r?pertoire de stockage des fichiers g?n?r?s"
Repertoire.Show
If Repertoire.SelectedItems.Count = 0 Then Exit Sub
MonRepertoire = Repertoire.SelectedItems(1) & "\"
Set wbk1 = ThisWorkbook
Set ws1 = wbk1.ActiveSheet
Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Offset(1, 0).ClearContents
monFichier = Dir(MonRepertoire & "*.xlsx")
Do While monFichier <> ""
ws1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select
derL = ws1.Cells(Rows.Count, 2).End(xlUp).Row + 1
Set wbk2 = Workbooks.Open(MonRepertoire & monFichier)
wbk2.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Cells.Copy
ws1.Paste
ws1.Range("A" & derL & ":A" & ws1.Cells(Rows.Count, 2).End(xlUp).Row) = wbk2.Name
Application.DisplayAlerts = False
wbk2.Close False
Application.DisplayAlerts = True
Rows(derL).Delete Shift:=xlUp
monFichier = Dir
Loop
Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Cells(1, 1).Select
derL1 = ws1.Cells(Rows.Count, 2).End(xlUp).Row + 1
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B2").Select
ActiveCell.FormulaR1C1 = "=isole(RC[-1])"
Range("B2").Select
Selection.AutoFill Destination:=Range("B" & derL1 & ":B" & ws1.Cells(Rows.Count, 2).End(xlUp).Row)
End SubMerci d'avance
T
C'est résolu