Copying data from one workbook to another

stevewood1

New Member
Joined
Oct 11, 2018
Messages
16
Hello,

Please could anyone help with the following query.

I have two separate workbooks that contain lists of case numbers in column B some of these numbers will be found in both workbooks and some will be unique.

For case numbers that are found on both the new workbook and were on the old workbook I want the Macro to copy the values from columns G - J of the old work book into the corresponding columns of the new workbook.

I found the following code

Sub Macro2()
'
' Macro2 Macro
'Sub Macro3()
'
' Macro3 Macro
'Sub copyOnMatch()


Dim i As Long
Dim j As Long
Dim wbk1, wbk2 As Workbook

Set wbk1 = ThisWorkbook.Sheets("Full Caselist")
WBK1Range = wbk1.Range("A" & Rows.Count).End(xlUp).Row
Set wbk2 = Workbooks.Open("\\\\DFZ70031.link2.gpn.gov.uk\90171312$\Aug 18 Reporting Caselist Vi copy.xlsm") 'Your Filepath here
WBK2Range = wbk2.Worksheets("Full Caselist").Range("A" & Rows.Count).End(xlUp).Row

For j = 1 To WBK1Range
For i = 1 To WBK2Range
If wbk1.Cells(j, 2).Value = wbk2.Worksheets("Full Caselist").Cells(i, 2).Value Then
wbk2.Worksheets("Full Caselist").Cells(j, 7).Value = wbk1.Cells(i, 7).Value
wbk2.Worksheets("Full Caselist").Cells(j, 8).Value = wbk1.Cells(i, 8).Value
wbk2.Worksheets("Full Caselist").Cells(j, 9).Value = wbk1.Cells(i, 9).Value
wbk2.Worksheets("Full Caselist").Cells(j, 10).Value = wbk1.Cells(i, 10).Value



Else
End If
Next i
Next j

I thought this would work however on inspection it does appear to be copying some of the data over but it doesn't appear to be in the same line as the case number that it is linked to on the older workbook.

Can anyone help me as to where I am going wrong?

Thanks,

Steve
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi & welcome to MrExcel.
How about
Code:
Sub CopyData()
   Dim Wbk As Workbook
   Dim Ows As Worksheet, Nws As Worksheet
   Dim Cl As Range
   
   Set Ows = ThisWorkbook.Sheets("Full Caselist")
   Set Wbk = Workbooks.Open("\\\\DFZ70031.link2.gpn.gov.uk\90171312$\Aug 18 Reporting Caselist Vi copy.xlsm")
   Set Nws = Wbk.Sheets("Full Caselist")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ows.Range("B1", Ows.Range("B" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 5).Resize(, 4)
      Next Cl
      For Each Cl In Nws.Range("B1", Nws.Range("B" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 5).Resize(, 4).Value = .Item(Cl.Value)
      Next Cl
   End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top