Migrate data from one workbook to another workbook using VBA

Wanted to extract matching data based on a cell from one workbook to another workbook when there is a match.

Normally I would get the old workbook  and migrate the data to the new workbook if the part numbers match using vlookup.  But I wanted to see if there is a way of doing this for each tab using VBA.

Attached is "OLD DATA" and "NEW DATA"

On the "OLD DATA" there is data starting from columns I thru r.  What I wanted is that same data to go into the "NEW DATA" if the part numbers match at column A then populate with the information from the "OLD DATA" starting from columns I thru R for each tab.
C--Users-lfreund-Downloads-NEW-DATA.xlsx
C--Users-lfreund-Downloads-OLD-DATA.xlsx
LUIS FREUNDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Hi,
You can loop through Excel sheet and copy data from one Book to another, like

   Do While True
        RowID = RowID + 1
        If Trim(Worksheets("Code Sheet").Cells(RowID, 5).Value) = "" Then
            Exit Do
        End If
        
        String0 = UCase(Trim(Worksheets("Budget-Report-HO").Cells(1, 5).Value))
        If InStr(Trim(Worksheets("Budget-Report-HO").Cells(2, 5).Value), " ") > 0 Then
            Site2 = UCase(Mid(Trim(Worksheets("Budget-Report-HO").Cells(2, 5).Value), 1, 5))
        Else
            Site2 = UCase(Trim(Worksheets("Budget-Report-HO").Cells(2, 5).Value))
        End If

Open in new window

0
LUIS FREUNDAuthor Commented:
New to vba...could you provide the workbook with it please?
0
HuaMin ChenProblem resolverCommented:
Try to create your codes and see this example
   Dim Message0 As String, RowID As Integer, File0 As String, File1 As String, Current_Book As String, File2 As String, Sheet_Exist As Boolean, Path0 As String, Path1 As String, Month0 As String, Country0 As String, SiteName As String, Title0 As String, Window0 As String, String0 As String
   Current_Book = Trim(ActiveWorkbook.Name)
   
   Call_From_Mod = "Close_Month_Button"
   
   Message0 = ""
    Unprotect_Workbook Message0
    
   Message0 = ""
   Path0 = Replace(Replace(ActiveWorkbook.Path, "/", "\"), "http:", "")
   Worksheets("Budget-Report-HO").Cells(6, 5).Value = "CLOSE"
    File0 = Path0 & "\..\Admin\AsiaPacific_Budget_HO.xlsm"
    File1 = Path0 & "\..\Admin\AsiaPacific_Budget_HO.xlsm"
    
    Message0 = ""
    Validate_Previous_Month Message0
    If Message0 <> "" Then
        Call_From_Mod = ""
        Exit Sub
    End If
    
   RowID = 0
   Dim Site2 As String
   Site2 = ""
   
   Do While True
        RowID = RowID + 1
        If Trim(Worksheets("Code Sheet").Cells(RowID, 5).Value) = "" Then
            Exit Do
        End If
        
        String0 = UCase(Trim(Worksheets("Budget-Report-HO").Cells(1, 5).Value))
        If InStr(Trim(Worksheets("Budget-Report-HO").Cells(2, 5).Value), " ") > 0 Then
            Site2 = UCase(Mid(Trim(Worksheets("Budget-Report-HO").Cells(2, 5).Value), 1, 5))
        Else
            Site2 = UCase(Trim(Worksheets("Budget-Report-HO").Cells(2, 5).Value))
        End If
        
        If Site2 <> "" Then
            If String0 = "CHINA" Or String0 = "HK_AREA" Then
                String0 = UCase(Trim(Worksheets("Budget-Report-HO").Cells(1, 5).Value)) & "-" & Site2
            End If
        End If
        'Debug.Print "xx"
        'Debug.Print Trim(Worksheets("Code Sheet").Cells(RowID, 5).Value) & String0 & Trim(Worksheets("Code Sheet").Cells(RowID, 6).Value) & Format(Worksheets("Budget-Report-HO").Cells(5, 5).Value, "yyyy/MM")
        If Trim(Worksheets("Code Sheet").Cells(RowID, 5).Value) = String0 And Trim(Worksheets("Code Sheet").Cells(RowID, 6).Value) = Format(Worksheets("Budget-Report-HO").Cells(5, 5).Value, "yyyy/MM") Then
            MsgBox "The month '" & Format(Worksheets("Budget-Report-HO").Cells(5, 5).Value, "yyyy/MMM") & "' of '" & String0 & "' has already been closed!"
            Protect_Workbook Message0
            Exit Sub
        End If
        
   Loop

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Luis,

Please give this a try.
The following code will prompt you to open the Old Data File and then fetch all the relevant data from column I:R into the New Data File.

Sub getMatchingDataFromOldFile()
Dim wbNew As Workbook, wbOld As Workbook
Dim wsNew As Worksheet, wsOld As Worksheet
Dim sFile, r
Dim slr As Long, i As Long
Dim arrNew, arrOld, oldIR()

Application.ScreenUpdating = False

Set wbNew = ThisWorkbook

sFile = Application.GetOpenFilename(FileFilter:="Excel Workbooks ,*.xls*", Title:="Open Old Data File", MultiSelect:=False)
If sFile = False Then
    MsgBox "You didn't select the Old Data File", vbExclamation, "Action Cancelled By User!"
    Exit Sub
End If
Set wbOld = Workbooks.Open(sFile)

For Each wsNew In wbNew.Sheets
    slr = wsNew.Cells(Rows.Count, 1).End(xlUp).Row
    arrNew = wsNew.Range("A2:A" & slr).Value
    ReDim oldIR(1 To UBound(arrNew, 1), 1 To 10)
    Set wsOld = getOldSheet(wsNew, wbOld)
    If Not wsOld Is Nothing Then
        arrOld = wsOld.Range("A1").CurrentRegion.Value
        For i = 1 To UBound(arrNew, 1)
            r = Application.Match(arrNew(i, 1), Application.Index(arrOld, , 1), 0)
            If Not IsError(r) Then
                oldIR(i, 1) = arrOld(i + 1, 9)
                oldIR(i, 2) = arrOld(i + 1, 10)
                oldIR(i, 3) = arrOld(i + 1, 11)
                oldIR(i, 4) = arrOld(i + 1, 12)
                oldIR(i, 5) = arrOld(i + 1, 13)
                oldIR(i, 6) = arrOld(i + 1, 14)
                oldIR(i, 7) = arrOld(i + 1, 15)
                oldIR(i, 8) = arrOld(i + 1, 16)
                oldIR(i, 9) = arrOld(i + 1, 17)
                oldIR(i, 10) = arrOld(i + 1, 18)
            End If
        Next i
        wsNew.Range("I2").Resize(UBound(oldIR), 10).Value = oldIR
        wsNew.Range("I2").Resize(UBound(oldIR), 10).WrapText = False
    End If
Next wsNew
wbOld.Close False
Application.ScreenUpdating = True
MsgBox "Data has been copied from Old Data File successfully.", vbInformation, "Done!"
End Sub


Function getOldSheet(ByVal wsN As Worksheet, ByVal wb As Workbook) As Worksheet
Dim ws As Worksheet
For Each ws In wb.Sheets
    If ws.Name = wsN.Name Then
        Set getOldSheet = ws
        Exit For
    End If
Next ws
End Function

Open in new window

In the attached, click the button called "Get Data From Old File" on "2+2" Sheet to run the code.
C-NEW-DATA.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LUIS FREUNDAuthor Commented:
AWESOME Neeraj!   Exactly what I needed.  Thank you very much......
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You'r welcome Luis!
0
LUIS FREUNDAuthor Commented:
Hi Neeraj,

I have a follow up question....should I repost similar so I can award points?

It looks like if I have a strikethrough in a sentence I get a Type Mismatch error....
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Sure. Open a new question and provide the sample file as well and highlight the cells which contain those values. And put the link of your new question here also.
0
LUIS FREUNDAuthor Commented:
Great...will do
0
LUIS FREUNDAuthor Commented:
Neeraj...just posted it.  Thanks!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Replied to your new question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.