View original (French)

VBA Excel: Import Sheets to a Workbook

Solved
Amélie -  
cs_Le Pivert Posted messages 8437 Status Contributor -
Hello,

I am looking for a reliable code that allows me to import sheets from one workbook to another.
Specifically, by opening the workbook containing the macro and then pressing a button, I would like to be able to retrieve all sheets from a workbook to choose but located in the same directory.
Does anyone know how to do this?

Thank you!

2 answers

  1.
    cs_Le Pivert Posted messages 8437 Status Contributor 730
     
    Press Alt F11 to access the editor
    Insert a UserForm
    Add a button with this code 

    Option Explicit Private Sub CommandButton1_Click() Dim i As Integer Dim nom As String Workbooks.Open ThisWorkbook.Path & "\" & "Classeur1.xls" 'workbook to copy adjust the name 'loop through all sheets For i = 1 To Worksheets.Count Workbooks("Classeur1.xls").Activate 'workbook to copy adjust the name nom = Worksheets(i).Name Sheets(nom).Select Sheets(nom).Copy after:=Workbooks("Copier_onglet.xls").Sheets(1) 'workbook to paste adjust the name Next End Sub


    --
    @+ Le Pivert
    9
    1. Amelie
       
      Hello,

      Thank you for the response! After trying the code, I can confirm that it works perfectly! The tabs don't come in the same order, but that's not important; what matters is there.

      Thanks again!
      0
    2. cs_Le Pivert Posted messages 8437 Status Contributor 730
       
      Yes, I noticed and I corrected it.
      I added the tabs clearing in case.
      We will need to add a button.
      Here is the code: 

      Option Explicit Private Sub CommandButton1_Click() Dim i As Integer Dim nom As String Workbooks.Open ThisWorkbook.Path & "\" & "Classeur1.xls" 'workbook to copy, adjust the name 'loop through all sheets For i = 1 To Worksheets.Count Workbooks("Classeur1.xls").Activate 'workbook to copy, adjust the name nom = Worksheets(i).Name Sheets(nom).Select Sheets(nom).Copy After:=Workbooks("Copier_onglet.xls").Sheets(1) 'workbook to paste, adjust the name Sheets(nom).Move After:=Sheets(Sheets.Count) 'organize the tabs Next End Sub Private Sub CommandButton2_Click() Sheets("Recapitulatif").Select ClearAllSheets End Sub Sub ClearAllSheets() Dim Ctr Application.DisplayAlerts = False For Ctr = Sheets.Count To 1 Step -1 If Sheets(Ctr).Name <> ActiveSheet.Name Then Sheets(Ctr).Delete End If Next Application.DisplayAlerts = True End Sub Private Sub UserForm_Initialize() CommandButton1.Caption = "Copy sheets" CommandButton2.Caption = "Delete sheets" End Sub


      Have a nice weekend
      @+ Le Pivert
      0