VB help in Combining Data from Multiple workbooks into a single workbook with same headers

[rac241985]

Hello Everyone,

Is there a way to combine data from multiple excel files into one single sheet by appending data? All the files have the same headers. Also these sheets have drop down list and conditional formatting. Is it possible to append the original data with all the conditional formatting as well?

Any help will be appreciated!

Thanks,

Rachit

 

[Coding4Fun]

Another thing you dont want the main workbook you are copying to in the same file as the workbooks you want to copy from.

You also said your main workbook was named "Hopper_Master" this file should be on your desktop then create a folder on your desktop and put the files you want to copy from in here.

StrFile should point to the folder that contains you files to copy from
Filepath should point to this same location
MasterFile should point to Master_Hopper on your desktop.

 

[rac241985]

I did copy the Hopper_Master on Desktop and have the remaining files in a folder to which the VB code is pointing.

The sheet in the Main workbook is called "Hopper" whereas in User 1 and User 2 workbooks is "Main".
Also the path contains the user name as you had mentioned.

StrFile = Dir("C:\\Users\\User1\\Desktop\\Test\\**")
Filepath = "C:\Users\User1\Desktop\Test"

MasterFile = "C:\Users\User1\Desktop\Hopper_Master.xlsm"

I have the VB code in the Main.xlsm workbook. Ran it again but to no sucess .

 

[Coding4Fun]

I keep seeing this

Filepath = "C:\Users\User1\Desktop\Test\" <--- Add this missing char in red and try again.

 

[rac241985]

Sorry that was typo in my last thread. I have it correct

Filepath = "C:\Users\User1\Desktop\Test\"

Thanks,

 

[Coding4Fun]

Ok I just tested this again and It works perfectly I am not sure why you are not able to get this to work for you.


On Desktop - Master_Hopper.xlsx
On Desktop - Excel Test Folder - This folder contains all the files that you want to copy the data from
On Desktop - Macro Woorkbook containing this code


Open the Macro book and run the macro.


Maybe try inserting a break in the code and then step through it and see if you can identify where it is not running properly for you.

 

[rac241985]

Finally! It works like Magic!! ... Thanks a ton!!!!

The problem was that I did not have a separate file for the Macro. I had the macro saved in the Master_Hopper xlsx.

I see that all the files in the excel test folder are open when I run the macro. Is there a way that excel does not open all the files from the test folder. I just want the Master_Hopper to open on the desktop.

Thanks,

 

[Coding4Fun]

Perfect! I am glad you figured it out.

As for your comment about opening the files, I think what you are trying to avoid is being left with a bunch of open workbooks that you now have to close

Give this a shot, it will close each file after it opens it and copies the data.

Sub LoopThroughFiles()

    Dim StrFile As String
    Dim Filepath As String
    Dim TempFile As Workbook
    Dim MasterFile As String

    StrFile = Dir("C:\\Users\\UserName\\Desktop\\Excel Test\\**")
    Filepath = "C:\Users\UserName\Desktop\Excel Test\"
    
    MasterFile = "C:\Users\UserName\Desktop\Hopper_Master.xlsx"
    Workbooks.Open Filename:=MasterFile
    Set MstFile = ActiveWorkbook
    MLR = MstFile.Sheets("Hopper").Cells(Rows.Count, "A").End(xlUp).Row
    
    Workbooks.Open Filename:=Filepath & StrFile
    Set TempFile = ActiveWorkbook
    
    LR = TempFile.Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
    
    TempFile.Sheets("Main").Range("A2:O" & LR).Copy
    MstFile.Sheets("Hopper").Cells(MLR + 1, 1).PasteSpecial
    
    Application.DisplayAlerts = False
    TempFile.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    Do While Len(StrFile) > 0
    
    StrFile = Dir()
    
    If Len(StrFile) = 0 Then
    Exit Do
    End If
    
    Workbooks.Open Filename:=Filepath & StrFile
    
    Set TempFile = ActiveWorkbook
    
    LR = TempFile.Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row

    MLR = MstFile.Sheets("Hopper").Cells(Rows.Count, "A").End(xlUp).Row
    
    TempFile.Sheets("Main").Range("A2:O" & LR).Copy
    MstFile.Sheets("Hopper").Cells(MLR + 1, 1).PasteSpecial
    
    Application.DisplayAlerts = False
    TempFile.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    Loop
    
End Sub

 

[Coding4Fun]

Hoping that I dont add confusion here but I did notice based on your previous post that you were trying to run this macro from within the Master_Hopper file if you do not want to house this macro in a seperate file you could put this code into the Master_Hopper.xlsm and it then will not open this file and can be ran from Master_Hopper.xlsm

So to clarify the previous version as you realized needs to be put into a separate Excel file, this version below can be entered directly into the Master_Hopper.xlsm file and ran from there.

Sub LoopThroughFiles2()

    Dim StrFile As String
    Dim Filepath As String
    Dim TempFile As Workbook
    Dim MasterFile As String

    StrFile = Dir("C:\\Users\\UserName\\Desktop\\Excel Test\\**")
    Filepath = "C:\Users\UserName\Desktop\Excel Test\"
    
    Set MstFile = ActiveWorkbook
    MLR = MstFile.Sheets("Hopper").Cells(Rows.Count, "A").End(xlUp).Row
    
    Workbooks.Open Filename:=Filepath & StrFile
    Set TempFile = ActiveWorkbook
    
    LR = TempFile.Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
    
    TempFile.Sheets("Main").Range("A2:O" & LR).Copy
    MstFile.Sheets("Hopper").Cells(MLR + 1, 1).PasteSpecial
    
    Application.DisplayAlerts = False
    TempFile.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    Do While Len(StrFile) > 0
    
    StrFile = Dir()
    
    If Len(StrFile) = 0 Then
    Exit Do
    End If
    
    Workbooks.Open Filename:=Filepath & StrFile
    
    Set TempFile = ActiveWorkbook
    
    LR = TempFile.Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row

    MLR = MstFile.Sheets("Hopper").Cells(Rows.Count, "A").End(xlUp).Row
    
    TempFile.Sheets("Main").Range("A2:O" & LR).Copy
    MstFile.Sheets("Hopper").Cells(MLR + 1, 1).PasteSpecial
    
    Application.DisplayAlerts = False
    TempFile.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    Loop

 

[rac241985]

Thanks a lot! That helps a lot.

However, is there a way to delete the previous data in the active sheet of Master_Hopper everytime the macro is run? I guess this will delete the headers. Not sure if I can preserve the headers in that case. Currently it just appends the data.

Another option is to clear the Master_Hopper workbook and run through the destination files and pick the header from the first file I open and then the remaining files can be appended from Row 2 such that the header is copied.

Can you please help out ?

Thanks

 

[Coding4Fun]

Add in the code in red below as shown

    Dim StrFile As String    
    Dim Filepath As String
    Dim TempFile As Workbook
    Dim MasterFile As String


    StrFile = Dir("C:\\Users\\UserName\\Desktop\\Excel Test\\**")
    Filepath = "C:\Users\UserName\Desktop\Excel Test\"
    
    Set MstFile = ActiveWorkbook
    MLR = MstFile.Sheets("Hopper").Cells(Rows.Count, "A").End(xlUp).Row
    [B][COLOR=#ff0000]MstFile.Sheets("Hopper").Rows("2:" & MLR).ClearContents[/COLOR][/B]