Astuces Excel
  1. Astuces Excel
  3. Script VBA pour split toutes les feuilles de calcul dans un classur pour séparer les files
Intereting Posts
C # Excellence de lancement COM Interop exception "Exception de HRESULT: 0x800A03EC" Comment choisir une cellule adjacente au nombre maximal dans une rangée Copier des loggings uniques d'un classur vers un autre classur principal ExcelPackage ne pas save par programme en utilisant C # Exception lors de la mise en surbrillance des duplicates Puis-je requestr à VBA de dormir pendant un certain time avant de continuer à la prochaine ligne de code? Impossible de faire correspondre le nom de la colonne Python Export Chart d'un xlsx existant Timestamp lorsqu'une cellule est modifiée via une formule (Excel) Sélection automatique d'une table Excel dans Visual Basic dans un complément Les cellules vides émettent lors de l'extraction de données Excel en utilisant POI dans JAVA? Informations agrégées dans les cellules enfant Niveau de présentation Excel Groupements Numéro de reference du classur temporaire non enregistré – time d'exécution Erreur 438 Comment utiliser une gamme de search variable en index match ou équivalent? Comment puis-je bien formater cela dans vba

Script VBA pour split toutes les feuilles de calcul dans un classur pour séparer les files

J'ai un script qui fait un vlookup pour chaque feuille dans le classur, puis divise chaque feuille de calcul dans son propre file. J'ai le script ci-dessous, mais ça ne fonctionne pas. La partie vlookup fonctionne bien, mais j'ai des problèmes avec la scission. Il ne manque pas et me donne une erreur, ça ne fait rien. 

Sub Splitbook() MyPath = "***Folder Location***" For Each sht In Workbooks("PO135 Division 1.xlsx").Worksheets sht.Copy ActiveSheet.Cells.Copy ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats ActiveWorkbook.SaveAs _ Filename:=MyPath & "\" & sht.Name & ".xlsx" ActiveWorkbook.Close savechanges:=False Next sht End Sub

J'ai besoin de split les files, puis les sauvegarder dans un dossier distinct (" Emplacement des dossiers ") – ce n'est qu'un espace réservé pour le moment, il serait mis à jour avant d'exécuter le script.

Des pensées? Appréciez l'aide!

Mettez cela dans un module régulier: 

 Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Sub NewWb () Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Dim ws As Worksheet Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Dim wbActive As Workbook Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Dim wbNew As Workbook Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Dim x As Single Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Application.ScreenUpdating = False Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Définir wbActive = ActiveWorkbook Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Pour chaque ws dans wbActive.Worksheets Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Définir wbNew = Workbooks.Add Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub ws.Copy Before: = wbNew.Sheets (1) Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub abc = "C: \ Files \" & ws.Name & ".xlsx" Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Application.DisplayAlerts = False Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub wbNew.Sheets ("Sheet1"). Supprimez Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Application.DisplayAlerts = True Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub wbNew.SaveAs Nom de file: = abc Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub wbNew.Close Saved = True Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Next ws Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Set ws = Nothing Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Définir wbActive = Nothing Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Set wbNew = Nothing Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub Application.ScreenUpdating = True Sub NewWb() Dim ws As Worksheet Dim wbActive As Workbook Dim wbNew As Workbook Dim x As Single Application.ScreenUpdating = False Set wbActive = ActiveWorkbook For Each ws In wbActive.Worksheets Set wbNew = Workbooks.Add ws.Copy Before:=wbNew.Sheets(1) abc = "C:\Files\" & ws.Name & ".xlsx" Application.DisplayAlerts = False wbNew.Sheets("Sheet1").Delete Application.DisplayAlerts = True wbNew.SaveAs Filename:=abc wbNew.Close Saved = True Next ws Set ws = Nothing Set wbActive = Nothing Set wbNew = Nothing Application.ScreenUpdating = True End Sub