[RESOLVED] Merge specific Column from multiple workbook into one work book
I have 10 excel workbook in a folder that I want to combine into one file. The problem is that these files contains more than 30 columns of extra data, that I do not need.
Further complicating things is that the column positions differ between workbooks and workbooks have differing number of columns. I want to create a macro that will go through and open each file, search for the columns I need, and then copy those columns of data and combine them into one master file.
For Ex. I have attached some sample files in that files contains some columns
in that i need to find ID header column on workbook 1 and copy to A column in Master file again find Password on workbook 1 copy the column and paste to B column in master file.
same like that on each file. Sample.zip
Re: Merge specific Column from multiple workbook into one work book
you can try like
Code:
pth = "c:\temp\rnye\"
Set tgt = Workbooks.Open(pth & "master file.xlsx")
For wb = 1 To 3 '10
Set src = Workbooks.Open(pth & "workbook" & wb & ".xlsx")
Set dest = tgt.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set src = ActiveWorkbook
With src.Sheets(1)
Set colh = .Range("1:1").Find("ID")
cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1
dest.Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
Set colh = .Range("1:1").Find("Password")
dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
End With
src.Close False
Next
this is totally untested as i ran out of time
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Merge specific Column from multiple workbook into one work book
Hi
Thanks Westconn. I have tried your code But i got Error while running this Code..
I'm new to VBA so please tell me where did i have wrong in this code?
Code:
Sub Merge()
pth = "C:\users\Desktop\MyFolder\CSA\Sample\"
Set tgt = Workbooks.Open(pth & "master file.xlsx")
For wb = 1 To 3 '10
Set src = Workbooks.Open(pth & "workbook" & wb & ".xlsx")
Set dest = tgt.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set src = ActiveWorkbook
With src.Sheets(1)
Set colh = .Range("1:1").Find("ID")
cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1
dest.Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
Set colh = .Range("1:1").Find("Password")
dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
End With
src.Close False
Next
End Sub
and Wokbooks dont have standard names (Like workbook 1, Workbook 2....this is for sample only), so i want merge the data from all the workbook in specific folder.
Re: Merge specific Column from multiple workbook into one work book
For the workbooks names issue, you will need to get the name of each workbook in the folder that you want to process.
Code:
Sub Merge()
Dim strFileName As String
Dim strFilesLike As String
Dim strPathName As String
Dim strCurrentFile As String
strPathName = "C:\users\Desktop\MyFolder\CSA\Sample\"
strFilesLike = "*.xls*"
strFileName = strPathName & strFilesLike
strCurrentFile = Dir(strFileName)
Do While strCurrentFile <> ""
' Combine file data code goes here
' Get next file to Import
strCurrentFile = Dir
Loop
End Sub
Re: Merge specific Column from multiple workbook into one work book
please tell me where did i have wrong in this code?
no idea!! which line does the error occur? the filename issue may be enough
you can incorporate the code posted by jdc to return all workbooks in a folder, using DIR, but make sure not to try to open master file if it is in the same folder
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Merge specific Column from multiple workbook into one work book
Hi Westconn
Really sorry for my Previous Post...I got error when i tested yesterday..
but today i have run the code this is working Perfectly.
Thank you so much. also thank you jdc for your code.
the problem is the work book name. i have incorporate your code with jdc code i got error and also it did not paste the data to Master file.
Re: Merge specific Column from multiple workbook into one work book
This the code i have incorporate with westconn's code
Code:
Sub Mersge()
Dim strFileName As String
Dim strFilesLike As String
Dim strPathName As String
Dim strCurrentFile As String
strPathName = "\\corp.ads\users\VDIUserDataQIDC1\x196446\Desktop\MyFolder\CSA\Sample\"
Set tgt = Workbooks.Open(strPathName & "master file.xlsx")
strFilesLike = "*.xls*"
strFileName = strPathName & strFilesLike
strCurrentFile = Dir(strFileName)
Do While strCurrentFile <> ""
' Combine file data code goes here
Set dest = tgt.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set src = ActiveWorkbook
With src.Sheets(1)
Set colh = .Range("1:1").Find("ID")
cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1
dest.Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
Set colh = .Range("1:1").Find("Password")
dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
End With
src.Close False
' Get next file to Import
strCurrentFile = Dir
Loop
End Sub
Re: Merge specific Column from multiple workbook into one work book
I have modified the VBA code now its working.. But this is merged three times of date in each file.
II have attached sample files, in this three file i have 6000 data's but after merging i have checked in Master file, 18000 data is there.
also some times its shown the error message like "we could not find the file ***.xlsx"...even file is there, at that time it was merged two files only.
This the code i have modified
Code:
Sub Merges()
Dim strFileName As String
Dim strFilesLike As String
Dim strPathName As String
Dim strCurrentFile As String
pth = "C:\users\Desktop\MyFolder\Master\"
Set tgt = Workbooks.Open(pth & "master file.xlsx")
strPathName = "C:\users\Desktop\MyFolder\Master\Sample\"
strFilesLike = "*.xls*"
strFileName = strPathName & strFilesLike
strCurrentFile = Dir(strFileName)
Do While strCurrentFile <> ""
' Combine file data code goes here
Set src = Workbooks.Open(strFileName)
Set dest = tgt.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set src = ActiveWorkbook
With src.Sheets("Sheet1")
Set colh = .Range("1:1").Find("ID")
cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1
dest.Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
Set colh = .Range("1:1").Find("Password")
dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
End With
src.Close False
' Get next file to Import
strCurrentFile = Dir
Loop
End Sub
Re: Merge specific Column from multiple workbook into one work book
and i got error on this line
you did not specify the error, but an error would occur if the value it was looking (in the sample, either ID or Password), was not found, looks like sheets("sheet1") was not the same as sheets(1), as the sample only had one sheet it would not have been a problem
i do not see any reason why the code, as you have posted, would copy the data 3 times
when i tested i got a total of 6363 rows from your sample files, so i believe that would be correct as workbook 3 had 6294 rows and i think the others had 34 each
the only issue was
Set src = Workbooks.Open(strFileName), should be
Code:
Set src = Workbooks.Open(strCurrentFile)
this just caused an error here, but maybe have some other effect for you
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Merge specific Column from multiple workbook into one work book
Set src = Workbooks.Open(strFileName), should be
Code:
Set src = Workbooks.Open(strCurrentFile)
Actually i got error when using this code as below image
Code:
i do not see any reason why the code, as you have posted, would copy the data 3 times
when i tested i got a total of 6363 rows from your sample files, so i believe that would be correct as workbook 3 had 6294 rows and i think the others had 34 each
yes its working with previously attached sample file..
But when run on my original files its merged three times..
Please see the attachment for original file.
Re: Merge specific Column from multiple workbook into one work book
I retested with the new sample, I also got the same error, I do not know why it worked this morning(different machine), but
Code:
Set src = Workbooks.Open(strPathName & strCurrentFile)
should fix it
the new sample showed rows count for each work book and the result being correct
6283
59
6
?cells(rows.Count,1).end(xlup).row
6349
no indication of 3 times copying
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Merge specific Column from multiple workbook into one work book
Thanks a lot.....
This is what i want.
Now its working the code
Code:
Set src = Workbooks.Open(strPathName & strCurrentFile)
I want to add one more thing on this, actually in some of the workbooks Header Name is not equal
for ex, in some workbook it is ID, in some workbooks it is USERNAME,
So i need find both header name, if any one available on that workbook it should be merged in master file A column(ID and USERNAME should be merge with A column.
could please help me where i have to modify the VBA for this.?
Re: Merge specific Column from multiple workbook into one work book
try like, where the first line is in the exisitng code, add the second line
Code:
Set colh = .Range("1:1").Find("ID")
if colh is nothing then set colh = .range("1:1").find("username")
if ID is not found, will look for username
of course really you should have appropriate code to handle any errors, or not finding either of the required columns, etc, etc
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Merge specific Column from multiple workbook into one work book
pls mark thread resolved
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
Hello,
Thank you for supporting on VBA code and one of my exact requirement and I'm new to vba coding or no knowledge at all
My requirement is: I have multiple excel files with multiple worksheet in it.
1. I need to copy data based on on the Column header. i.e. 'Column 1' placed in 'Column A' in one excelfile whereas 'Column 1' placed in 'Column AA' in another excelfile.
2. I need to create Masterfile where identical worksheet data will be copied from multiple files into master file sheet1
Please support me to fix this error....Thanks in advance!
Tried below code and it gives me error like: Run time error '1004' Application defined or object defined error marked in RED (tried with .xls*, .xlsx as well with .xlsm)
Sub Mersge()
Dim strFileName As String
Dim strFilesLike As String
Dim strPathName As String
Dim strCurrentFile As String
strCurrentFile = Dir(strFileName)
Do While strCurrentFile <> ""
' Combine file data code goes here
Set dest = tgt.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set src = Workbooks.Open(strPathName & strCurrentFile)
With src.Sheets(1)
Set colh = .Range("1:1").Find("ID")
cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1 dest.Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value Set colh = .Range("1:1").Find("Password")
dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
End With
src.Close False
' Get next file to Import
strCurrentFile = Dir
Loop
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
the code ran fine, without error, once i fixed some errors in the initial setup, i did not check the results were 100% correct, i will leave that for you to do
fix these lines
Code:
strPathName = "C:\temp\Sample\" ' all file paths were incorrect
Code:
Do While strCurrentFile <> ""
If InStr(strCurrentFile, "Master") = 0 Then 'see below note
' Combine file data code goes here
Set dest = tgt.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set src = Workbooks.Open(strPathName & strCurrentFile)
With src.Sheets(1)
Set colh = .Range("1:1").Find("ID")
cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1
dest.Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
Set colh = .Range("1:1").Find("Password")
dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
End With
src.Close False
End If
' Get next file to Import
strCurrentFile = Dir
Loop
the sample directory contained 2 master files that would cause error if processed, there are other methods that can achieve similar results, which is best to use depends on what other workbooks the folder may contain
edit: the first may have been an issue with the forum truncating the trailing back slash
Last edited by westconn1; Jun 25th, 2020 at 07:02 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
Hello westconn1,
Thanks a lot for fix...it worked well
now in my excel data I have column 'A' header as fixed (let's say 'ID' however if there is another column header with 'scope ID' it picks data from 'scope ID' instead only ID) and wanted to copy data as per column 'A' header where as other column header may change in their order. If I use above code it doesn't worked well (is there something which can be done with exact match).
Is there a possibility to add file name and systemdate against each entry by adding extra two columns?
Thanks,
Raj
Last edited by rajeshmh; Jun 26th, 2020 at 04:30 AM.
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
Is there a possibility to add file name and systemdate against each entry by adding extra two columns?
yes that is easy enough
If I use above code it doesn't worked well (is there something which can be done with exact match).
i am sure that can be done, but you will have to explain better
Also, I wanted to copy multiple column data based on Column A header
again better explanation required
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
Hi,
My comments inline....
Is there a possibility to add file name and systemdate against each entry by adding extra two columns?
yes that is easy enough: Gr8
If I use above code it doesn't worked well (is there something which can be done with exact match).
i am sure that can be done, but you will have to explain better
Ans: refer below code I used and its output attached. In output file instead of VM name it populate information for HA VM Monitoring
code:
Sub Mersge()
Dim strFileName As String
Dim strFilesLike As String
Dim strPathName As String
Dim strCurrentFile As String
Do While strCurrentFile <> ""
If InStr(strCurrentFile, "Master") = 0 Then 'see below note
' Combine file data code goes here
Set dest = tgt.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set src = Workbooks.Open(strPathName & strCurrentFile)
With src.Sheets(1)
Set colh = .Range("1:1").Find("VM")
cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1
dest.Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
Set colh = .Range("1:1").Find("CPU")
dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
End With
src.Close False
End If
' Get next file to Import
strCurrentFile = Dir
Loop
End Sub
Also, I wanted to copy multiple column data based on Column A header
again better explanation required
Ans: this is fixed and can be ignored
Last edited by rajeshmh; Jun 26th, 2020 at 06:01 AM.
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
In output file instead of VM name it populate information for HA VM Monitoring
change to
Code:
Set colh = .Range("1:1").Find("VM", , , xlWhole)
find starts from the first cell in the range to be searched so column A would be the last cell to be searched it the range 1:1
to add file name and systemdate against each entry by adding extra two columns?
try like
Code:
Set colh = .Range("1:1").Find("CPU")
dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
dest.Offset(, 3).Resize(cnt).Value = src.Name
dest.Offset(, 4).Resize(cnt).Value = Date
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
Hello westconn1,
Code worked as expected! thanks again for helping me out here
Now the last thing is, I have multiple worksheet in my source excel like sheet1, sheet2 etc... and current code export data from sheet1 of the source file. What shall be the code I can use which will copy data from sourcefile based on sheet1 data to master file of sheet1 and sheet2 data to master file of sheet2 and so on...Also I have specified names to Sheet1 like VM inventory, Sheet2 with CPUconfig etc...
Output I'm looking:
1. Master file VM inventory sheet shall be updated withSource file VM inventory sheet
2. Master file CPUconfig sheet shall updated withSource file CPUconfig sheet
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
Code:
Sub Mersge()
Dim strFileName As String
Dim strFilesLike As String
Dim strPathName As String
Dim strCurrentFile As String
strPathName = "C:\temp\sample\Sample\"
Set tgt = Workbooks.Open(strPathName & "masterfile.xlsx")
strFilesLike = "*.xls*"
strFileName = strPathName & strFilesLike
strCurrentFile = Dir(strFileName)
Do While strCurrentFile <> ""
If InStr(strCurrentFile, "Master") = 0 Then 'see below note
' Combine file data code goes here
'Set dest = tgt.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set src = Workbooks.Open(strPathName & strCurrentFile)
For Each sht In src.Sheets
With sht
Set dest = tgt.Sheets(sht.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Set colh = .Range("1:1").Find("VM", , , xlWhole)
cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1
dest.Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
Set colh = .Range("1:1").Find("CPU")
dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
dest.Offset(, 3).Resize(cnt).Value = src.Name
dest.Offset(, 4).Resize(cnt).Value = Date
End With
Next
src.Close False
End If
' Get next file to Import
strCurrentFile = Dir
Loop
End Sub
this is not really tested, but should copy for each sheet in the source workbook, i have not tested if the same sheet exists in the master workbook, obviously an error will occur if there is no sheet of the same name
of course if the column headers are different in each worksheet then the code will not copy any data, so that will have to be revisited
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
you did not explain what needed to be fixed, as far as i could see with a brief look the code should do as requested
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
Hi,
I'm looking source file i.e. book1, book2 which has 3 sheets in each like 'VMInventory', 'NIConfig' and 'CPUConfig'. Data of these files to be copied to Master file i.e. in Master file again 3 sheets are there with same name.
In summary:
1. data from book1/2 worksheet 'VMInventory' to be copied to master file worksheet 'VMInventory'
2. data from book1/2 worksheet 'NIConfig' to be copied to master file worksheet 'NIConfig'
3. data from book1/2 worksheet 'CPUConfig' to be copied to master file worksheet 'CPUConfig'
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
like 'VMInventory', 'NIConfig' and 'CPUConfig'. Data of these files to be copied to Master file i.e. in Master file again 3 sheets are there with same name.
the code i posted should do that, what is actually happening?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Merge specific Column from multiple workbook into one work book
Hi westconn1,
1. code copy the data where column 'A' in worksheet has VM i.e. in VMInventory and NICConfig for workbook1 only and error out at dest.Offset(, 1).Resize(cnt).Value = colh.Offset(1).Resize(cnt).Value
2. code only writes sourcefile and date to 1st sheet i.e. VMInventory, it will be good to repeat this to two other files
3. in CPUConfig, Column 'A' is Host, so data for Host to be copied which is not the case .Not sure if code looks for VM column and its defined Set colh = .Range("1:1").Find("VM", , , xlWhole)
is there a way sheet name can be specified in code and I can use two codes to copy data from my source file sheet.