VBA Excel: Import Sheets to a Workbook
Solved
Amélie
-
cs_Le Pivert Posted messages 8437 Status Contributor -
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!
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
Press Alt F11 to access the editor
Insert a UserForm
Add a button with this code
--
@+ Le Pivert
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
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!
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 SubHave a nice weekend
@+ Le Pivert