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]

What would your process be?
Are you working with the same files each time, same names?
are the files always in a specific location or does that change?
Will you have all files open when running the macro or should the macro open the files?
So you want to copy the contents of each workbook and add it to the bottom of a specific workbook?
The workbook you are copying everything to: will this be created before or should this be created in the process?

 

[rac241985]

What would your process be?
My process will be to append the data from 30 files from a sheet name called "Main" from every excel workbook and copy these in a Master workbook in a sheet called "Hopper"

Are you working with the same files each time, same names?
Yes same files with the same names

are the files always in a specific location or does that change?
No all the files will be in the same location

Will you have all files open when running the macro or should the macro open the files?
I want the marco to open the files

So you want to copy the contents of each workbook and add it to the bottom of a specific workbook?
I want the contents in Master workbook in one specific tab to refresh and copy the data from all workbooks from specific work sheet every time the macro is used.

The workbook you are copying everything to: will this be created before or should this be created in the process?
Preference is that this workbook is already created and only one specific worksheet is refreshed every time. There are other sheets in this workbook which I do not want to be edited.

Also I want to copy all the contents with formatting, formulas copies to the main work sheet if possible .

Appreciate your help in anyway possible!

Thanks

 

[Coding4Fun]

What would your process be?
My process will be to append the data from 30 files from a sheet name called "Main" from every excel workbook and copy these in a Master workbook in a sheet called "Hopper"
Copy data from 30 files
Each of the 30 files has a sheet named "Main" this is where we want to copy the data FROM
Range to copy?
Paste data to Master Workbook
Name of Master File?
Paste each copied range to the end of current data in Master Workbook? File one has 7 rows of data, copy this into master workbook, then copy from file to and paste starting on row 9 (The header and the 7 rows remaining)

So you want to copy the contents of each workbook and add it to the bottom of a specific workbook?
I want the contents in Master workbook in one specific tab to refresh and copy the data from all workbooks from specific work sheet every time the macro is used.
Range to copy?

The workbook you are copying everything to: will this be created before or should this be created in the process?
Preference is that this workbook is already created and only one specific worksheet is refreshed every time. There are other sheets in this workbook which I do not want to be edited.
Pasting to "Hopper" correct?

 

[rac241985]

What would your process be?
My process will be to append the data from 30 files from a sheet name called "Main" from every excel workbook and copy these in a Master workbook in a sheet called "Hopper"
Copy data from 30 files
Each of the 30 files has a sheet named "Main" this is where we want to copy the data FROM -Yes
Range to copy? -The Range is not defined. It has fixed number of columns (15). The rows will vary.
Paste data to Master Workbook -Yes
Name of Master File? -Hopper_Master
Paste each copied range to the end of current data in Master Workbook? File one has 7 rows of data, copy this into master workbook, then copy from file to and paste starting on row 9 (The header and the 7 rows remaining) -Yes from File 2 onwards only copy the data without the header

So you want to copy the contents of each workbook and add it to the bottom of a specific workbook?
I want the contents in Master workbook in one specific tab to refresh and copy the data from all workbooks from specific work sheet every time the macro is used.
Range to copy? - Fixed number of columns (15) but the rows may vary (1 to 100).

The workbook you are copying everything to: will this be created before or should this be created in the process?
Preference is that this workbook is already created and only one specific worksheet is refreshed every time. There are other sheets in this workbook which I do not want to be edited.
Pasting to "Hopper" correct? Yes the Hopper_Master File has a worksheet named Hopper. This Hopper worksheet has the same number of columns as other files and has some conditional formatting and drop down list based on other sheets in this file. When i run this Macro is it possible to retain the conditional formatting.

Thanks,

 

[Coding4Fun]

Ok one last question round sorry....

Does the file that you are copying from contain the same conditional formatting and lists as the destination file OR is the conditional formatting and lists only present in the destination file and you want to preserve this.

Please clarify where the conditional formatting is and the expected result.

 

[rac241985]

The file from where I will be copying will have the same conditional formatting as the destination file.

Thanks,

 

[Coding4Fun]

Try this, in this example I store the Master file on my desktop and all the files I want to open and copy are in a folder on my desktop called Excel Test.

It will loop through EVERY file in this folder, open each one copy the range paste to master file....rinse and repeat for all files in the Excel Test folder.

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
    
    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
    
    Loop
    
End Sub

 

[rac241985]

Hi,

I tried running the code but it does not append the data. It does not give me any error . This is what I have in the Master excel sheet

Test A	Test B	Test C	Test D
1	2	3	4
2	3	4	1
RP	RP	RP	4
RP	RP	RP	4

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

This is what is present in the User 1 and User 2 excel workbooks

User 1

Test A	Test B	Test C	Test D
1	2	3	4
1	2	3	4
1	2	3	4
1	2	3	4

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

User 2

Test A	Test B	Test C	Test D
4	2	3	1
4	2	3	1
4	2	3	1
4	2	3	1

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Here is my slightly modified code

Sub LoopThroughFiles()
Dim StrFile As String
Dim Filepath As String
Dim TempFile As Workbook
Dim MasterFile As String




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

MasterFile = "C:\Users\Desktop\Test\Main.xlsm"
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:D" & LR).Copy
MstFile.Sheets("Hopper").Cells(MLR + 1, 1).PasteSpecial

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:D" & LR).Copy
MstFile.Sheets("Hopper").Cells(MLR + 1, 1).PasteSpecial

Loop

End Sub

Thanks,

 

[Coding4Fun]

If you are actually using the code exactly as you have it in your previous post your issue will be here.

StrFile = Dir("C:\\Users\\[COLOR=#ff0000]Your User Name Goes Here\\[/COLOR][COLOR=#8b4513]Desktop\\Test\\**")[/COLOR]
[COLOR=#8b4513]Filepath = "C:\Users\[/COLOR][COLOR=#FF0000]Your User Name Goes Here\[/COLOR][COLOR=#8b4513]Desktop\Test[/COLOR][COLOR=#ff0000]\[/COLOR][COLOR=#8b4513]"[/COLOR]

[COLOR=#8b4513]MasterFile = "C:\Users\[/COLOR][COLOR=#FF0000]Your User Name Goes Here\[/COLOR][COLOR=#8b4513]Desktop\Test\Main.xlsm"[/COLOR]

Example my desktop path is

C:\Users\Coding4Fun\Desktop\Test\