Can we browse and copy all the sheets

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
273
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
273
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
40,719
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
273
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
40,719
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,099,132
Messages
5,466,857
Members
406,505
Latest member
jvdm

This Week's Hot Topics

Top