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

rac241985

New Member
Joined
Jan 30, 2017
Messages
27
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

Active Member
Joined
Feb 16, 2017
Messages
344
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

New Member
Joined
Jan 30, 2017
Messages
27
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
 
Last edited:

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
344
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

New Member
Joined
Jan 30, 2017
Messages
27
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

Active Member
Joined
Feb 16, 2017
Messages
344
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

New Member
Joined
Jan 30, 2017
Messages
27
The file from where I will be copying will have the same conditional formatting as the destination file.

Thanks,
 

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
344
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.

Code:
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
 
Last edited:

rac241985

New Member
Joined
Jan 30, 2017
Messages
27
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 ATest BTest CTest D
1234
2341
RPRPRP4
RPRPRP4

<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 ATest BTest CTest D
1234
1234
1234
1234

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

User 2

Test ATest BTest CTest D
4231
4231
4231
4231

<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

Active Member
Joined
Feb 16, 2017
Messages
344
If you are actually using the code exactly as you have it in your previous post your issue will be here.

Code:
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\
 
Last edited:

Forum statistics

Threads
1,082,044
Messages
5,362,843
Members
400,694
Latest member
Sofie17

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top