Announcement

Collapse
No announcement yet.

VBA Code to Combine files

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Code to Combine files

    Hi guys,

    I'm having problems creating an efficient code that will go in a folder and get 4 files and combine them into one workbook with 4 tabs for each file and then add two additional file title Total 1 and Total 2. My problem is that I can seem to be able to name my location variables and declare them.

    The four files are contained here C:\Users\Allan Johnson\Desktop\Project 13. and they are titled Vendor.xlsx, Employees.xlsx, Cleaners.xlsx, and Misc.xlsx. Each new tab will have the file name. The new file will be titled Operating Epenses.xlsx and closed at the end. The macro will be accessed from a file titled Master.xlsm.

    Thank you in advance.

  • #2
    Re: VBA Code to Combine files

    If you have existing code, then please post it... preferably with copies of your workbook(s) with sample data.

    Comment


    • #3
      Re: VBA Code to Combine files

      Project 13.zip
      Originally posted by cytop View Post
      If you have existing code, then please post it... preferably with copies of your workbook(s) with sample data.
      Hi Cytop,

      I deleted the code that I had last night because I couldn't get it to move. However, I have attached some files that I have. Please note I took all the day to reduce space.

      Also there will be other files in that folder so I just want to get the named files.

      Thanks for the help.

      Comment


      • #4
        Re: VBA Code to Combine files

        Confused - you mention "... into one workbook with 4 tabs for each file" then upload empty workbooks with 1 named tab. Can only go on your workbooks as uploaded so the following code loads the named tab from each workbook into a new, unnamed, workbook.

        Code:
        Sub x()
            
            '// Change to suit
            Const strPath As String = "C:\TEMP\"
            
            Dim cFiles() As Variant
            Dim cFile As Variant
            
            Dim wb As Excel.Workbook
            Dim wbOpExp As Excel.Workbook
            
            cFiles = Array("Vendors.xlsx", "Employees.xlsx", "Cleaners.xlsx", "Misc.xlsx")
            
            Set wbOpExp = Application.Workbooks.Add
            
            For Each cFile In cFiles
                
                Set wb = Workbooks.Open(Filename:=strPath & cFile)
                
                wb.Sheets(Left$(cFile, InStr(cFile, ".") - 1)).Copy Before:=wbOpExp.Sheets(1)
                
                wb.Close savechanges:=xlDoNotSaveChanges
                Set wb = Nothing
                
            Next
            
            
        End Sub
        As the workbooks are empty, no titles, no suggestion as to the data layout or anything, there's nothing more I can suggest.

        Comment

        Working...
        X