Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Question Copy Multiple excel file to one master

    Hi All,

    I need a code that go in specific folder then copy the multiple files wroksheets to master file?

    Thanks and Regards,
    farrukh

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You were not very specific. Can you modify this? It takes all the workbooks in "C:\MyPath\" (change as desired) and adds the first worksheet to the end of the active sheet

    Steve
    Code:
    Option Explicit
    Sub CombineMultipleFiles()
    ' Path - modify as needed but keep trailing backslash
      Const sPath = "C:\MyPath\"
      Dim sFile As String
      Dim wbkSource As Workbook
      Dim wSource As Worksheet
      Dim wTarget As Worksheet
      Dim lRows As Long
      Dim lMaxSourceRow As Long
      Dim lMaxTargetRow As Long
      'Dim blnNoHeader As Boolean
      
      On Error GoTo ErrHandler
      Application.ScreenUpdating = False
      
      Set wTarget = ActiveSheet
      lRows = wTarget.Rows.Count
      sFile = Dir(sPath & "*.xls*")
      Do While Not sFile = ""
        Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
        Set wSource = wbkSource.Worksheets(1)
        lMaxSourceRow = wSource.Cells(lRows, 1).End(xlUp).Row
        lMaxTargetRow = wTarget.Cells(lRows, 1).End(xlUp).Row
        wSource.Range("1:" & lMaxSourceRow).Copy _
          Destination:=wTarget.Cells(lMaxTargetRow + 1, 1)
        wbkSource.Close SaveChanges:=False
        sFile = Dir
      Loop
      
    ExitHandler:
      Application.ScreenUpdating = True
      Exit Sub
      
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub

  4. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    HI sdckapr,

    I am getting error application defined or object defined error ?

    Thanks and Regards,
    Farrukh

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Which line gives the error and under what circumstances?

    Steve

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2011-06-08)

  7. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Sorry i it works fine that was my fault, Steve i am testing the code put two excel work book in the folder path C:\MyPath\. The code opens both excel file but copy only one worksheet from one excel file i have 30,30 excel worksheet in workbooks ,but just getting the one excel sheet into the master any help please...

    Thanks and Regards,
    farrukh
    Last edited by farrukh; 2011-06-05 at 13:03.

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Change the line
    Set wSource = wbkSource.Worksheets(1)
    to:
    For Each wSource In wbkSource.Worksheets

    And add the Line "Next" like below:

    wSource.Range("1:" & lMaxSourceRow).Copy _
    Destination:=wTarget.Cells(lMaxTargetRow + 1, 1)
    Next
    wbkSource.Close SaveChanges:=False

    This will loop through all the worksheets in each of the workbooks that it opens instead of just using the first worksheet.

    Steve

  9. #7
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Hi steve i have change the lines but still it getting only one sheet to master?

    Code:
    Sub CombineMultipleFiles()
    ' Path - modify as needed but keep trailing backslash
      Const sPath = "D:\path\"
      Dim sFile As String
      Dim wbkSource As Workbook
      Dim wSource As Worksheet
      Dim wTarget As Worksheet
      Dim lRows As Long
      Dim lMaxSourceRow As Long
      Dim lMaxTargetRow As Long
      'Dim blnNoHeader As Boolean
      
      On Error GoTo ErrHandler
      Application.ScreenUpdating = False
      
      Set wTarget = ActiveSheet
      lRows = wTarget.Rows.Count
      sFile = Dir(sPath & "*.xls*")
      Do While Not sFile = ""
        Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
        For Each wSource In wbkSource.Worksheets
        lMaxSourceRow = wSource.Cells(lRows, 1).End(xlUp).Row
        lMaxTargetRow = wTarget.Cells(lRows, 1).End(xlUp).Row
        wSource.Range("1:" & lMaxSourceRow).Copy _
          Destination:=wTarget.Cells(lMaxTargetRow + 1, 1)
          Next
        wbkSource.Close SaveChanges:=False
        sFile = Dir
      Loop
      
    exithandler:
      Application.ScreenUpdating = True
      Exit Sub
      
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume exithandler
    End Sub
    Thanks and Regards,
    farrukh

  10. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Could you detail what you are testing with and what the results are? Also what you want the results to be.

    It works in my testing. All the worksheets in all the workbooks end up in the activesheet.

    Steve

  11. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2011-06-08)

  12. #9
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Hi Steve sorry i am not making you understand. Suppose i have 10 workbook in a folder and each workbook has few worksheets. I want each workbook (worksheet) copy to master file (with different tabs).

    Thanks for helping me
    farrukh
    Last edited by farrukh; 2011-06-07 at 18:16.

  13. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Is this what you are after?
    Steve

    Code:
    Option Explicit
    Sub CopyWorksheets()
    ' Path - modify as needed but keep trailing backslash
      Const sPath = "C:\MyPath\"
      Dim sFile As String
      Dim wbkSource As Workbook
      Dim wSource As Worksheet
      Dim wbkTarget As Workbook
      
      On Error GoTo ErrHandler
      Application.ScreenUpdating = False
      
      Set wbkTarget = ActiveWorkbook
      sFile = Dir(sPath & "*.xls*")
      Do While Not sFile = ""
        Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
        For Each wSource In wbkSource.Worksheets
          With wbkTarget
            wSource.Copy After:=.Sheets(.Sheets.Count)
          End With
        Next
        wbkSource.Close SaveChanges:=False
        sFile = Dir
      Loop
      
    ExitHandler:
      Application.ScreenUpdating = True
      Exit Sub
      
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub

  14. The Following 2 Users Say Thank You to sdckapr For This Useful Post:

    farrukh (2011-06-08),galang_ofel (2013-04-25)

  15. #11
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    173
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Dear Steve,

    That is too wonder full this is my need you have done it,Thank alot

    Farrukh
    Last edited by farrukh; 2011-06-08 at 14:06.

  16. #12
    New Lounger
    Join Date
    Sep 2011
    Location
    australia
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Do you have a code that go in specific folder let say C:/Path/List/

    1.) On that folder there are multiple List excel files (List 1.xls, List 2.xls etc) having 3 sheets (TabName1, TabName2, TabName3) on each List#.xls

    2.) Is there a way to only copy a specific tab name (eg just TabName2) in a Master file? TabName2 is unique per excel file

    3.) Can I also have a macro that Master file will populate all the excel in C:/Path/List/, if it is exsiting in the master file it will not generate another copy of the TabName2

    Thanks in advance

  17. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You should be able to modify the code attached yourself...
    1) change the line of the code with the new path. The path is explicitly written into the code
    2) Don't loop through all the worksheets, just copy the desired one
    3) Not sure I understand, but it seems that you want to check the existence of a worksheet into the file and only copy if it doesn't exist. add that as an IF to the code to check if the worksheet name is already into the target worksheet.

    [Item 3 is a little confusing. Once you open up the first workbook and copy the TabName2 from it into the master, what would be the point of opening up any other workbooks since at this stage you know it exists in the master since you just added it?]
    Steve

  18. #14
    New Lounger
    Join Date
    Sep 2011
    Location
    australia
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for your response Steve, I have no backgrounnd in VBA, can i ask you to provide the correct code please. Sorry!

    1) change the line of the code with the new path. The path is explicitly written into the code
    done with this...
    2) Don't loop through all the worksheets, just copy the desired one
    I need to loop all the files from the source folder to make sure that whatever changes, the master will get it.

    3) Not sure I understand, but it seems that you want to check the existence of a worksheet into the file and only copy if it doesn't exist. add that as an IF to the code to check if the worksheet name is already into the target worksheet.
    can I ask a sample If code for this. You're correct I need to copy the document not yet existing or just to overwrite the existing document to the latest one.

  19. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    My goal is to help teach you VBA if you are going to work with it, not do all your work for you.

    You can loop through all the files, just don't loop through all the worksheets in each file, if you only want to copy 1 particular worksheet. But you still haven't explained exactly what you want done. If the first workbook opened has the worksheet of interest and it gets copied into the master, there seems to be no need to open any other ones as the worksheet now exists in the master, so no other ones would be copied...

    This makes me conclude that I don't understand what you want, so there is no need to write code. Perhaps you should detail the steps you want to do. Make it simple assume there are only a master and 3 other files in the folder to work with and excel is closed. Walk me through what you would do manually, when you run the code and what the code would do...

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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