Can we browse and copy all the sheets

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
266
Hi Everyone ,

Can we browse and copy all the sheets to Master sheet.

Like if i browse for a "Abc" excel workbook, it should copy all the sheets that are available in "Abc" workbook and paste that in Master workbook

Can this be done any suggestion?????

Here is what i tried
Code:
Sub Browse_And_Add_Sheets()
Dim ws As Worksheet
Range("A2").Select
myfile = Application.GetOpenFilename(, , "Browse for Workbooks")


Workbooks.Open myfile
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "Live Employees" Then
        Sheets("Live Employees").Copy After:=Workbooks("Master").Sheets(1)
        Sheets("Master").Select
    ElseIf ws.Name Like "EsiReport" Then
        Sheets("EsiReport").Copy After:=Workbooks("Master").Sheets(1)
        Sheets("Master").Select
Next ws


Windows("Master.xlsm").Activate
End Sub
But from the above code it searched for that sheet name , instead of that i need to copy all the sheets that are available

Thanks in advance
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
266
Hi Everyone ,

Can we browse and copy all the sheets to Master sheet.

Like if i browse for a "Abc" excel workbook, it should copy all the sheets that are available in "Abc" workbook and paste that in Master workbook

Can this be done any suggestion?????

Here is what i tried
Code:
Sub Browse_And_Add_Sheets()
Dim ws As Worksheet
Range("A2").Select
myfile = Application.GetOpenFilename(, , "Browse for Workbooks")


Workbooks.Open myfile
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "Live Employees" Then
        Sheets("Live Employees").Copy After:=Workbooks("Master").Sheets(1)
        Sheets("Master").Select
    ElseIf ws.Name Like "EsiReport" Then
        Sheets("EsiReport").Copy After:=Workbooks("Master").Sheets(1)
        Sheets("Master").Select
Next ws


Windows("Master.xlsm").Activate
End Sub
But from the above code it searched for that sheet name , instead of that i need to copy all the sheets that are available

Thanks in advance
Can this be done , any Ideas are appreciated
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,071
Office Version
365
Platform
Windows
How about
Code:
Sub GirishDhruva()
   Dim Ws As Worksheet
   Dim Wbk As Workbook
   Dim Fname As String
   
   Fname = Application.GetOpenFilename(, , "Browse for Workbooks")
   Set Wbk = Workbooks.Open(Fname)
   
   For Each Ws In Wbk.Worksheets
      Ws.Copy , ThisWorkbook.Sheets(1)
   Next Ws
   Wbk.Close False
End Sub
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
266
How about
Code:
Sub GirishDhruva()
   Dim Ws As Worksheet
   Dim Wbk As Workbook
   Dim Fname As String
   
   Fname = Application.GetOpenFilename(, , "Browse for Workbooks")
   Set Wbk = Workbooks.Open(Fname)
   
   For Each Ws In Wbk.Worksheets
      Ws.Copy , ThisWorkbook.Sheets(1)
   Next Ws
   Wbk.Close False
End Sub
Thanks @Fluff it worked perfectly
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,071
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.

Also please do not quote entire posts, as it just clutters up the thread.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,096,321
Messages
5,449,698
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top