Consolidate Multiple Files based on Column Headers in VBA
HTML-код
- Опубликовано: 17 июл 2022
- #renamemultiplefoldersatonce,#Getallfoldername,#renamefolder
In this video, you will learn how to consolidate multiple files based on Column Header and browse folders in Excel VBA.
Download Practice File:
drive.google.com/drive/folder...
EXCEL DASHBOARD PLAYLIST:
EXCEL DASHBOARD: • EXCEL DASHBOARD
EXCEL VBA PLAYLIST:
VBA SERIES ZERO TO PRO: • The A - Z Of Vba Intro...
VBA Brain Breaker
VBA Brain Breaker: • VBA Brain Breaker
ADVANCED ARRAY SERIES PLAYLIST:
MASTERING IN ARRAY FORMULAS: • How to use Array in ex...
ADVANCED EXCEL PLAYLIST:
SIMPLE TO ADVANCED EXCEL SERIES IN HINDI: • How to get the sum of ...
MIS INTERVIEW QUESTIONS & ANSWERS PLAYLIST:
Excel Interview Question & Answer | MIS Interview #1: • Excel Interview Questi...
MIS Interview Question and Answer #2: • MIS Interview Success:...
MIS Interview Question and Answer #3: • Cracking the MIS Inter...
Frequently Asked Interview Questions | You should Know: • Frequently Asked Inter...
ACCESS AUTOMATION PLAYLIST:
Import Data from Access Database to Excel | Access Automation: • Import Data from Acces...
Populate Access DB Data into ListBox & Combobox in VBA: • Populate Access DB Dat...
Import Access DB Data into Excel with multiple conditions: • Import Access db Data ...
#Consolidatedatabasedonheaders
#ConsolidateMultiplefilebasedonheader
#excelvba
#vbaconsolidate
Wow learnt something new here. Great videi
Thanks for sharing your knowledge
Very well explained
what a wonderful way of explaining things..amazing videio
Really your video very useful us
This is such helpful information
Very informative video
Thank u for sharing these kind of informational videos always so helpful stay blessed
Good way of learning
Wow its really amazing video
Nice sharing Mehandi queen
What a wonderful video
Greate video.. well explained
You are very knowledgeable and great tutorial in explaining things !!
Here again to continue with the end
very well explained..thanks for sharing
Nicely explained thanks for sharing with us👍
Great sharing nice explain
Nice sharing 👍
Great video 👍
Very good sir
Great video nice sharing
Thank you so much
This is so helpful
Nicely explained thanks for sharing 👌
Cookfood diary
Wow this is awesome
Great sharing like always, keep it up!
Nice sharing
Very informative video ushared thnku so much
Excellent Wow great information 👍
So great I came here twice
Amazing informative videos thank u so much for sharing
Very good
Amazing good job
Useful bna coding sir
Great sharing
What a wonder video
Awesome!!
Great sharing, keep it up!
best useful
V Informative thanks for Sharing
Great explanation
Great!
Excellent. Appreciate the manner in which you explained a complex process. Watched it 6 times today and was finally able to apply this in a real life scenario. Thank you so much.
Thank you for your kind words.
Well explained! Thank you
Well explained instructions
Very informative. Thanks for sharing!
Great information shared
🤗🤗🤗🤗🤗 Nice
Amazing
Wow this was really good !!
Love it!
Muito bom seu vídeo 🤗🤗🤗🤗
👍
Nice!
great explanation 👍
Great content ❤️
Thank you for sharing this. When I worked in a worksheet range was OK. But when I converted my worksheet to a table format, it didn't work anymore. I need in table format to allow nice slicer (Auto filter feature in Excel) to work in different sheet tab. Can you help me Sir? Thanks in advance.
We would be delighted to assist you. If possible, kindly send me some sample files at levelupexcelskill@gmail.com. I'll automate this.
Very nic
K m beauty
how can we copy using input box value -- autofilter
from mutlple WB to paste master workbook
You can use application.inputbox("Select range", Type:=8)
You can share a sample file if you want to automate
Please help to understand multiple file and multiple sheet based on Column Headers?
Please find the below code for your reference
Sub ConsolidateFiles()
Dim folderPath As String
Dim targetWorkbook As Workbook
Dim ws As Worksheet
Dim fileName As String
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim lastRow As Long
Dim headerColumnIndex As Variant
' Set the target workbook (where you want to consolidate the data)
Set targetWorkbook = Workbooks.Add
' Specify the folder path containing the Excel files to consolidate
folderPath = "C:\Path\To\Your\Folder\"
' Loop through each file in the folder
fileName = Dir(folderPath & "*.xlsx")
Do While fileName ""
' Open the source workbook
Set sourceWorkbook = Workbooks.Open(folderPath & fileName)
' Loop through each sheet in the source workbook
For Each sourceSheet In sourceWorkbook.Sheets
' Find the column index of the specified header
headerColumnIndex = Application.Match("YourColumnHeader", sourceSheet.Rows(1), 0)
' Check if the header exists (headerColumnIndex is not an error)
If Not IsError(headerColumnIndex) Then
' Find the last row in the target sheet
Set ws = targetWorkbook.Sheets.Add
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
' Copy data from the source sheet to the target sheet
sourceSheet.UsedRange.Copy ws.Cells(lastRow, 1)
End If
Next sourceSheet
' Close the source workbook without saving changes
sourceWorkbook.Close False
' Get the next file in the folder
fileName = Dir
Loop
End Sub
Thanks for this kind of videos. It's very helpful session, it but I have few query please assist me how can I contact you.
Email me at levelupexcelskill@gmail.com if you wish to get in touch.
Thanks for sharing vba coding for consol files, but getting debug error on 'set ws =wb.Activesheet'
Please run in a breakpoint mode then check why it is showing. Any doubts please send me your code or sample file.
Thanks,can we share your email id to get better understanding
@@niteshyadav217 Ayush75031@gmail.com
I have an problem that having inconsistence header in multiple woeksheets and thoes header we have map then consolidate. Can you please guide on this problem statement
Yes, please share the same file with us on our email id levelupexcelskill@gmail.com
@@levelupexcelskill i have shared the file over an above mail
@@abhishekawari919 shared please check
If first row blank macro does not work
You can customize as per your requirement
I have a question. Can I apply this to when I am looking for to copy paste data into one sheet, from a ‘Summary’ sheet of 67 different workbooks? Would that copy paste data from different workbooks one after the other or would that overwrite the previous data?
Yes, it will work, but you will need to change some lines of code. For example, I've mentioned the sheet name as Report sheet and my data starts in row 6. This deletes the existing old data in Report sheets and consolidates multiple workbooks. If you want your data to remain the same, you must delete this code.
code like: SHT.Range("C6:J1048576").Clear
This will help you to consolidate multiple workbooks if some code is modified according to your workbook and then run it
Sub CONSOLIDATEFILE()
Dim SHT As Worksheet
Dim PATH As String
Dim FILENAME As String
Dim WB As Workbook
Dim WS As Worksheet
Dim lR As Long
Dim CL As Long
Dim NEWROW As Long
Dim I As Long
Dim MT As Long
Set SHT = ThisWorkbook.Worksheets("Report") 'Change sheet name as per your sheet
SHT.Range("C6:J1048576").Clear 'if you wish to keep old data then delete this code from here
PATH = SHT.Range("E2")
FILENAME = Dir(PATH & "*.XL*")
Do While FILENAME ""
Set WB = Workbooks.Open(PATH & FILENAME, False, True)
Set WS = WB.ActiveSheet
lR = WS.Range("A1048576").End(xlUp).Row
NEWROW = SHT.Range("C1048576").End(xlUp).Row + 1
CL = SHT.Range("XFD5").End(xlToLeft).Column
For I = 3 To CL
If Not IsError(Application.Match(SHT.Cells(5, I), WS.Range("1:1"), 0)) Then
MT = Application.Match(SHT.Cells(5, I), WS.Range("1:1"), 0)
WB.Activate
WS.Activate
WS.Range(WS.Cells(2, MT), WS.Cells(lR, MT)).Copy
ThisWorkbook.Activate
SHT.Activate
SHT.Cells(NEWROW, I).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End If
Next
Application.DisplayAlerts = False
WB.Close
Application.DisplayAlerts = True
FILENAME = Dir
Loop
SHT.Range("C6", SHT.Range("J6").End(xlDown)).Borders.LineStyle = xlContinuous
SHT.Range("C6", SHT.Range("J6").End(xlDown)).Borders.Color = RGB(217, 217, 217)
SHT.Range("C6", SHT.Range("J6").End(xlDown)).HorizontalAlignment = xlCenter
SHT.Range("C6", SHT.Range("J6").End(xlDown)).VerticalAlignment = xlCenter
End Sub
Thank you for your response, and appreciate you taking time out of your busy schedule and writing the code for me! I am very new to vba, hence the confusion. I wouldn’t be able to tell the difference much. Can I explain the situation here, and ask if the code that you have provided would work in my case or not?
I have 67 separate workbooks. Each workbook has 4 worksheets, out of which I just want to copy data from the sheet named ‘Summary’, to another separate workbook. The data from those 67 ‘Summary’ sheets should copy one after the other without deleting the previous workbook’s Summary’ sheet data. Would the code that you’ve provided work in this situation? Thank you in advance and your response would be highly appreciated!
@@payeldey870 Yes, it will work, but we need to add a little additional code. Please provide me with a sample file so I can create a macro and describe each line of code. Levelupexcelskill@gmail.com is my email address
Thank you Sir. I sent you the sample random data files to your above mentioned email address for further guidance. Please let me know if that’s possible.
@@payeldey870 Sent Template file
how to use .csv FILE
You can change the source code
How to collate multiple sheets one file using this coding.
Please share your email address because you will need to add some additional lines and loops to get data. I'll email the information to you.
sir, how to get file name in cell
You can simply add code wb.name below the loop
Can u share vba code?
@Nitesh Yadav, you can download the file from the description box that I mentioned.
Very well explained
Great video 👍
Great sharing, keep it up!
Very informative video