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