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

R

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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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?
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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,

 
Upvote 0
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.
 
Upvote 0
The file from where I will be copying will have the same conditional formatting as the destination file.

Thanks,
 
Upvote 0
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:
Upvote 0
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

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Test A[/TD]
[TD="width: 64"]Test B[/TD]
[TD="width: 64"]Test C[/TD]
[TD="width: 64"]Test D[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]RP[/TD]
[TD="class: xl63"]RP[/TD]
[TD="class: xl63"]RP[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]RP[/TD]
[TD="class: xl63"]RP[/TD]
[TD="class: xl63"]RP[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
</tbody>[/TABLE]

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

User 1
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Test A[/TD]
[TD="width: 64"]Test B[/TD]
[TD="width: 64"]Test C[/TD]
[TD="width: 64"]Test D[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
</tbody>[/TABLE]

User 2
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Test A[/TD]
[TD="width: 64"]Test B[/TD]
[TD="width: 64"]Test C[/TD]
[TD="width: 64"]Test D[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
</tbody>[/TABLE]


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,
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
VBA - Append CSV data to current file
2
Replies
12
Views
860
Domenic
D
E
  • Solved
Appended Row Data (Gantt Style)
Replies
3
Views
143
felixstraube
F
A
  • Question Question
populate data based on Matching headers for multiple sheets across files
Replies
0
Views
171
Amer Omar
A
L
  • Question Question
vba x powepoint data extraction
Replies
1
Views
147
Dan_W
D
F
  • Question Question
Multiple workbooks with multiple sheets in each
Replies
1
Views
287
alansidman
A

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top