Results 1 to 4 of 4

Thread: Copy data from multiple workbooks into separate tabs

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    1
    Rep Power
    0

    Lightbulb Copy data from multiple workbooks into separate tabs

    This is great information. Is there a simple way to edit this to consolidate the worksheets into one workbook, creating a separate tab for each consolidated worksheet, instead of all data on one sheet? Thanks in advance...

  2. #2
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    What is great information ? A simple way to edit what ?
    Before we can help you out you will have to give us some more information about what you want to achieve.

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Untested.

    Code:
    Option Explicit
    
    Sub kTest()
        
        Dim SourceFolder    As String
        Dim wbkSource       As Workbook
        Dim wbkDest         As Workbook
        Dim FName           As String
        Dim rngSource       As Range
        Dim wksDest         As Worksheet
        
        SourceFolder = "C:\Test" '<<< adjust the folder
        
        If Right$(SourceFolder, 1) <> "\" Then SourceFolder = SourceFolder & "\"
        
        FName = Dir(SourceFolder & "*.xls*")
        
        Set wbkDest = ThisWorkbook
        Application.ScreenUpdating = 0
        Set wksDest = wbkDest.Worksheets(wbkDest.Worksheets.Count)
        
        Do While Not FName = vbNullString
            If Not FName = wbkDest.Name Then
                Set wbkSource = Workbooks.Open(SourceFolder & FName, 0)
                Set rngSource = wbkSource.Worksheets(1).Range("a1").CurrentRegion
                Set wksDest = wbkDest.Worksheets.Add(, wksDest)
                rngSource.Copy wksDest.Range("a1")
                wksDest.Name = FName
            End If
            wbkSource.Close 0
            Set wbkSource = Nothing
            FName = Dir()
        Loop
        Application.ScreenUpdating = 1
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Quote Originally Posted by bakerman View Post
    What is great information ? A simple way to edit what ?
    Before we can help you out you will have to give us some more information about what you want to achieve.
    @ bakerman,
    The OP was refering thread - http://www.excelfox.com/forum/f13/co...-file-vba-569/
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 10
    Last Post: 08-31-2013, 06:56 PM
  2. Replies: 6
    Last Post: 07-10-2013, 11:28 PM
  3. Replies: 2
    Last Post: 05-28-2013, 05:32 PM
  4. Replies: 3
    Last Post: 05-14-2013, 03:25 PM
  5. Replies: 2
    Last Post: 11-08-2012, 01:15 PM

Posting Permissions

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