Results 1 to 5 of 5

Thread: Can we browse and copy all the sheets

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Location
    India
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Can we browse and copy all the sheets

    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

  2. #2
    Board Regular
    Join Date
    Mar 2019
    Location
    India
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can we browse and copy all the sheets

    Quote Originally Posted by GirishDhruva View Post
    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

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,149
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Can we browse and copy all the sheets

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #4
    Board Regular
    Join Date
    Mar 2019
    Location
    India
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can we browse and copy all the sheets

    Quote Originally Posted by Fluff View Post
    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

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,149
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Can we browse and copy all the sheets

    You're welcome & thanks for the feedback.

    Also please do not quote entire posts, as it just clutters up the thread.
    Last edited by Fluff; May 2nd, 2019 at 08:10 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •