Can we browse and copy all the sheets

[GirishDhruva]

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

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

 

[GirishDhruva]

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

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]

How about

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]

How about

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]

You're welcome & thanks for the feedback.

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