Printable View
Hello all,
Id like to know if there is a vba code that will combine excel sheets from a workbook in a given folder the data should be consolidated based on headers.
Ex : say i have a folder that has workbooks from 4 different people so a vba code that will ask me to choose a folder which has all workbooks that needs to be consolidated and save it to same folder
Hi Mayer392 and Welcome to this forum. It doesn't seem like you're having much luck with this thread. I'm guessing the reason being that your question is not clear. Sheets are not the same as workbooks. Sure U can extract sheets from different workbooks and put them into one workbook... I have no idea what "consolidated based on headers" means? Putting a whole bunch of workbooks into 1 workbook... what's the point? Anyways, if you're still looking for help, please provide more specific information. HTH. Dave
Sure dave, will soon
And a lot of people just skip a thread that says "VBA Help Needed" I think that it is safe to say that that counts for nearly 100 percent of the threads.
If you can't be bothered to come up with a proper thread title, we can't be bothered to help.
Hello All,
Firstly thanks for pointing my mistakes in creating the thread.
I'm now attaching a zip file that has 2 workbooks WB1.xlsx and WB2.xlsx and a folder called consolidated that has consolidated excel file.
WB1 has 3 sheets, WB2 has 2 sheets; both the workbooks have data and the first row is considered to be header, based on which the data from other sheets needs to be added up (if the header is present or created if missing)
So when i run the code, a msg box would popup asking for location of files, once the location is chosen then it would create a folder named consolidated, consolidate the excel sheets and then place the excel sheet into consolidate folder.
Please let me know if further information is needed.
EDIT : How can i change the thread's title ?
As for the name of my thread, my apologies but im really bad at naming things, deciding a subject line for an email and so on... so i'm not sure what do i change the Title to, suggestions are welcome!
For now changing to Consolidating files in a folder based on Header
So... 1) Select wbs to be consolidated
2) Both have headers... where? In every sheet? If the header is not present then it is created? Based on headers what data needs to be added up?
3) After selecting files a folder is created where? Consolidate what sheets into what workbook sheet? I'm guessing U mean collect the data from some sheet(s) in some workbook and placing the data in a sheet in some other workbook?
4) Save the workbook in the consolidated folder. What is the wb/file name and what is the folder name and address. What if the folder exists? What if the file exists
Are U going to be attempting to provide any code? Dave
p.s Is VBAX planning on fixing this annoying interface? Add can't scroll thread when using quick reply to the list of annoyances. Cursor shows busy circle... grrr
Quote:
Originally Posted by Dave
Post the question in the "Site & Forum Related" section under "VBAX Issues"
1) Select wbs to be consolidated
2) Both have headers... where? - the very first row is considered as header
In every sheet? - Yes
If the header is not present then it is created? - Yes
Based on headers what data needs to be added up? Column data of respective header
[Please refer to the attachment once may be it will provide more clarity]
3) After selecting files a folder is created where? The folder from where the files to be consolidated are chosen
Consolidate what sheets into what workbook sheet? All Sheets across all the workboks to one Final workbook with one sheet
I'm guessing U mean collect the data from some sheet(s) in some workbook and placing the data in a sheet in some other workbook? - yes even this need can arise but i believe we can modify the code to choose specific sheets (if needed)
4) Save the workbook in the consolidated folder. What is the wb/file name and what is the folder name and address. What if the folder exists? What if the file exists - i guess i have mentioned that the file would be named Consolidated* (in order to avoid duplicacy datr / time stamp can be added)
Are U going to be attempting to provide any code? I have bits n pieces of codes that i can share but honestly im new to vba and not that skilled at it.
That being said im not shying to learn eithr
Still not clear on the header thing and adding them up. Let's start with getting all the sheets into 1 workbook. Place this code in sheet code and call/run testthat. Dave
Code:Option Explicit
Private Sub testthat()
Dim FileNm As Object, Cnt As Integer
Dim TargetFiles As FileDialog, sht As Worksheet, Cnt2 As Integer
'prompt user to select files
Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
With TargetFiles
.AllowMultiSelect = True
.Title = "Multi-select target data files:"
.ButtonName = ""
.Filters.Clear
.Filters.Add ".xlsx files", "*.xlsx"
.Show
End With
If TargetFiles.SelectedItems.Count = 0 Then
MsgBox "PICK A FILE!"
Exit Sub
End If
On Error GoTo Below
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Cnt2 = ThisWorkbook.Sheets.Count
For Cnt = 1 To TargetFiles.SelectedItems.Count
'open the file and assign the workbook/worksheet
Set FileNm = Workbooks.Open(TargetFiles.SelectedItems(Cnt))
For Each sht In Workbooks(FileNm.Name).Worksheets
sht.Copy After:=ThisWorkbook.Sheets(Cnt2)
Cnt2 = Cnt2 + 1
Next sht
Workbooks(FileNm.Name).Close SaveChanges:=False
Next Cnt
Below:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
If Err.Number <> 0 Then MsgBox "File Error"
End Sub
Ok Dave, will try and share the cores soon
Btw can you or someone help me in telling the basics so that i can code
U may want to change this line above. U have to learn by doing, googling, reading, copy and pasting, and asking questions when U can't get it to work. U have asked for a whole solution for a fairly involved outcome. It would be better to asked for solutions to smaller segments of the outcome but knowing the big picture is essential before starting. That's why U should write/map out in point form what needs to happen before U start. DaveCode:.Filters.Add "*.xls* files", "*.xls*"