View original (French)

Excel VBA Macro for Copying and Pasting Between Workbooks

Solved
tom_3031 Posted messages 8 Status Member -  
regys1 Posted messages 4 Registration date   Status Member Last intervention   -
Bonjour,

It is better to have one who knows than ten who search...

What lines of code should I include in my Excel macro to solve the following problem?

I have 52 Excel workbooks (in the same folder) from which I am interested in the content of about 10 cells. These cells are in the same sheet, in the same row and same column in all 52 workbooks. The cells are not contiguous.

I would like to copy/paste all this into a final independent workbook that has already been created. With 52 columns (1 for each workbook) of 10 rows (one for each cell).

=> Guiding idea of a contestable procedure to be translated into VBA

0) Create my macro from my final workbook with the following steps:
1) Start a loop over all the workbooks in the folder (specify the folder address)
Within the loop:
2) Open the first Excel
3) Copy and paste the 10 cells in the same column (empty) of the current workbook in the desired order
4) Copy and paste this column into its destination column in the final workbook
5) Close the Excel
6) Move to the next one

While I know how to manage step 3 within a single file, I am struggling with the management of switching from one workbook to another...

Between Workbooks, workbookS, Activate, Open, For each next, the Save a macro function, and not really knowing the VBA syntax... I'm struggling!

Thank you,

Thomas

7 answers

  1.
    pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Here is the translation: So,
    - Create a new Excel workbook
    - Place the macro below in a module,
    - Save your workbook as Recap.xslm (an Excel workbook that supports macros)
    - Make sure that your Recap.xslm workbook contains at least two sheets named Feuil1 and Feuil2
    - Test...

    Code: 
    Option Explicit Sub Import() Dim objShell As Object, objFolder As Object Dim Path As String, file As String Dim Column As Byte 'opening the folder selection window Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.BrowseForFolder(&H0&, "Choose a directory", &H1&) 'If the user cancels without choosing If objFolder Is Nothing Then 'message MsgBox "Operation canceled", vbCritical, "Cancellation" Else 'otherwise 'Path = chosen directory Path = objFolder.ParentFolder.ParseName(objFolder.Title).Path & "\" 'Choose the 1st file file = Dir(Path & "*.xls*") 'Column = column number where we will paste the data 'to start column A, leave at 0, to start column B replace 0 with 1 etc... Column = 0 'loop through all excel files in the chosen directory Do While Len(file) > 0 Column = Column + 1 If file <> ThisWorkbook.Name Then 'assign a name in the workbook, referring to the range to import: B2:I18 ThisWorkbook.Names.Add "Range", RefersTo:="='" & Path & "[" & file & "]site sheet'!$B$2:$I$18" With Sheets("Feuil2") ' "Import data" using the name given above .[B2:I18] = "=Range" .[B3:B10].Copy 'Copy B3:B10 End With With Sheets("Feuil1") .Cells(1, Column).PasteSpecial xlPasteValues 'Paste B3:B10 End With With Sheets("Feuil2") .[B16].Copy 'Copy B16 End With With Sheets("Feuil1") .Cells(9, Column).PasteSpecial xlPasteValues 'Paste B16 End With With Sheets("Feuil2") .[B18].Copy 'copy B18 End With With Sheets("Feuil1") .Cells(10, Column).PasteSpecial xlPasteValues 'Paste B18 End With With Sheets("Feuil2") .[G2].Copy 'Copy G2 End With With Sheets("Feuil1") .Cells(11, Column).PasteSpecial xlPasteValues 'Paste G2 End With With Sheets("Feuil2") .[I2].Copy 'Copy I2 End With With Sheets("Feuil1") .Cells(12, Column).PasteSpecial xlPasteValues 'Paste I2 End With With Sheets("Feuil2") .[G3].Copy 'Copy G3 End With With Sheets("Feuil1") .Cells(13, Column).PasteSpecial xlPasteValues 'Paste G3 End With With Sheets("Feuil2") .[I3].Copy 'Copy I3 End With With Sheets("Feuil1") .Cells(14, Column).PasteSpecial xlPasteValues 'Paste I3 End With End If file = Dir() Loop End If End Sub


    There are other ways to proceed that could, in particular, speed up the process.
    So come back and let us know if this suits you.
    --
    Sincerely,
    Franck P
    4
    1. Ledave
       
      Bonjour, je viens de découvrir ce qu'est une macro, et en fouillant sur le web, j'ai trouvé votre exemple que je cherche à adapter mais je n'y comprends rien en VBA. Ce que je souhaite faire, c'est dans un fichier Excel (tout joli tout beau) récupérer les valeurs de la colonne B du fichier 1 et les valeurs des colonnes A,B,C,D,E,F,G,H,K,L du fichier 2 et les coller dans le fichier Excel (tout joli tout beau). D'après ce que j'ai compris, il y aurait donc 2 boucles, une pour chaque fichier.... D'autre part, ayant copié/collé le code dans l'éditeur, puis-je être assuré que ce qui est en bleu est la condition, en noir la commande et en vert le commentaire.... Merci d'avance pour votre aide, ça m'éviterait de fastidieux copier-coller.
      0
    2. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Hello Ledave,
      Since each Excel file is unique, I recommend that you create your own topic and clearly outline your problem there. Feel free to copy-paste my code...
      0