Results 1 to 29 of 29

Thread: [RESOLVED] Merge specific Column from multiple workbook into one work book

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    Resolved [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


    Please anyone Help me.

    Thanks in advance

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    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.

    Thanks again.

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    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

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  6. #6

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    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.

    Please see the attached image .



    Thanks again both of you.Name:  Error_VBA.png
Views: 1265
Size:  2.6 KB

  7. #7
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: Merge specific Column from multiple workbook into one work book

    Post the complete code that you are using (in code tags) and indicate the line where you are getting the error.

  8. #8

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    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

    and i got error on this line
    Code:
     cnt = Cells(Rows.Count, colh.Column).End(xlUp).Row - 1

  9. #9

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    Exclamation 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
    Sample (2).zip

    Thanks Again.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  11. #11

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    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
    Name:  Runtime error.png
Views: 1209
Size:  3.1 KB

    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.

    Sample (3).zip

    Thanks

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  13. #13

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    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.?

    Thanks

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  15. #15

    Thread Starter
    Member
    Join Date
    Sep 2017
    Posts
    54

    Re: Merge specific Column from multiple workbook into one work book

    Yes..it's worked Perfectly....Thank you so much westconn and Jdc for your help.
    I have learned few things in by this Post


    Thanks again

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  17. #17
    New Member
    Join Date
    Jun 2020
    Posts
    7

    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

    strPathName = "C:\ExcelMerge\Sample"
    Set tgt = Workbooks.Open(strPathName & "master.xlsm")
    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 = 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

    End Sub
    Attached Files Attached Files

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  19. #19
    New Member
    Join Date
    Jun 2020
    Posts
    7

    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.

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  21. #21
    New Member
    Join Date
    Jun 2020
    Posts
    7

    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

    strPathName = "C:\temp\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)
    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
    Attached Files Attached Files
    Last edited by rajeshmh; Jun 26th, 2020 at 06:01 AM.

  22. #22
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  23. #23
    New Member
    Join Date
    Jun 2020
    Posts
    7

    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

    Thanks,
    Raj

  24. #24
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  25. #25
    New Member
    Join Date
    Jun 2020
    Posts
    7

    Re: [RESOLVED] Merge specific Column from multiple workbook into one work book

    Hello westconn1,

    Thanks for supporting and sorry for the confusion

    I have added now the sheets and I'm looking to copy data from source file to Master file as per defined worksheets.

    Thanks,
    Raj
    Attached Files Attached Files

  26. #26
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  27. #27
    New Member
    Join Date
    Jun 2020
    Posts
    7

    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'

    Thanks,
    Raj

  28. #28
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  29. #29
    New Member
    Join Date
    Jun 2020
    Posts
    7

    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.

    Thanks,
    Raj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width