How to Merge Data from Two Excel Worksheets

How to merge data from Two Excel worksheets in a new workbook and create a new database. We learn how to attach a recorded and edited macro to a button.
Details also available on my website:


  • Nice

  • Sondra Brooks

    Excellent instructions! Thank you!

  • Sabine Hutter

    Thank you!!! So useful!!!

  • Thanks for posting this video and it's very helpful! I do have a question though – i tried joining the two tables using a common ID variable; however, it wasn't successful as the message says that I have too many columns … is there a limit in terms of the number of columns when we merge data from two worksheets?

  • Alexandra Buylova

    Thank you!!

  • Dinesh. WOW. Can't wait to try this. Thank you soooo much

  • thx…

  • Crazy Spitfire


  • Sir you are best 🙂

  • Takyar sir, simple you are awesome. Your way of teaching and ideas are excellent. Thanks a ton!

  • developer option available in office 2010 sir and i have one doubt, what is the formula of date transfer from one sheet to another sheet.

  • oh my God… excel at his Finest!!!

  • Wow! I wish I had know about this last year. I have been exporting sheets to Access tables, joining the tables there then exporting it back to Excel. Doing it from a command button will help enormously. Thank you so much.

  • Very helpful. Thank you.

  • it is very fruitful ..thanks.

  • tamil chennai

    Dear sir . how to hide zero value rows in specific table then after update number the same row automatically un hide the same row please your advice .Thanks


    hi sir his is the code i am using

    # Sub LoopThroughDirectory()
    Dim MyFile As String, Filepath As String, rng As Range, wb As Workbook, i As Long, j As Long
    MyFile = Dir("E:data extract")
    Filepath = "E:data extract"
    Do While Len(MyFile) > 0
    If MyFile = "MASTER.xlsm" Then
    Exit Sub
    End If
    Set wb = Workbooks.Open(Filepath & MyFile)
    With wb.Sheets(1)
    rngArr = Array("B7", "B8", "A12", "B12", "C12", "D12", "F12", "H12", "H7", "H8")
    j = 0
    For i = LBound(rngArr) To UBound(rngArr)
    j = j + 1
    .Range(rngArr(i)).Copy Workbooks("MASTER.xlsm").Sheets(1).Cells(Rows.Count, j).End(xlUp)(2)
    End With
    MyFile = Dir
    End Sub


    Hi Sir, i love ur videos and i learned a lot…but i have some problem in transferring data from multiple WB sheets to master WB… your VBA is working good but…i have a random rows in my invoices which needs to be extracted so i will use only the selected rows but it will be common in all the invoices….and in such selected rows sometimes the selected cells will be blank in some invoices…so when coping it will paste the data to the previous blank cell so the complete data will be compromised…so sir please give me a solution for that…so that altho the column is blank it should be pasted in the same row and column only….PLEASE HELP ME SIR ….I will post my code below which i am using now….

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.