Hi folks, i'm back again and reworking a previous post that didn't get much traction.
I'm trying to loop through all values in Col 'A' of my sheet (TgtSht) and compare them with all values in Col 'A' of a worksheet (SrcSht) in an external workbook (wbSource). If the value in SrcSht Col 'A' does not exist in TrgSht and it meets the condition that SrcSht Col 'C' is "F6" and SrcSht Col 'W' is not "Archive", then the value from SrcSht Col 'A' is appended to the last row of TgtSht Col 'A'.
so i have the following code already... :
Now, whilst it does work the first time through, any subsequent run of the code duplicates all values in "A" of SrcSht, whether they exist in TgtSht or not. In other words, the code is simply appending all values in SrcSht where Col C = "F6" and Col W <> "Archive" rather then checking first if that value exists in TgtSht Col A already.
I know it must be a relatively simple fix, but i've been at it a while now and i cant see the wood for the trees and am officially putting out an S.O.S for assistance.
I'm trying to loop through all values in Col 'A' of my sheet (TgtSht) and compare them with all values in Col 'A' of a worksheet (SrcSht) in an external workbook (wbSource). If the value in SrcSht Col 'A' does not exist in TrgSht and it meets the condition that SrcSht Col 'C' is "F6" and SrcSht Col 'W' is not "Archive", then the value from SrcSht Col 'A' is appended to the last row of TgtSht Col 'A'.
so i have the following code already... :
VBA Code:
Sub CopyRow()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim SrcLastRow As Long
Dim TgtLastRow As Long
Dim iCounter As Integer
Dim rslt As Range, look As Range
Dim Cell As String
Dim exists As Boolean
Dim x As New Excel.Application
Dim wbSource As Workbook
Dim SrcSht As Worksheet
Dim TgtSht As Worksheet
Set wbSource = x.Workbooks.Open("my_External_excel_file_name.xlsm")
Set SrcSht = wbSource.Sheets("My_File")
Set TgtSht = ThisWorkbook.Worksheets("My_File_Tab")
Set rslt = TgtSht.Range("A8:A1500")
SrcLastRow = SrcSht.Range("A" & Application.Rows.Count).End(xlUp).Row
For iCounter = 790 To SrcLastRow
exists = False
Set look = rslt.find(SrcSht.Range("A" & iCounter).Value, , xlValues, xlWhole)
TgtLastRow = TgtSht.Range("A" & Application.Rows.Count).End(xlUp).Row
If Not look Is Nothing Then
Cell = look.Address
Do
If SrcSht.Range("A" & iCounter).Value = TgtSht.Range("A:A").Value Then
exists = True
Exit Do
End If
Set look = rslt.FindNext(look)
Loop While Not look Is Nothing And look.Address <> Cell
End If
If exists = False And _
SrcSht.Range("C" & iCounter).Value = "F6" And _
SrcSht.Range("W" & iCounter).Value <> "Archive" Then
SrcSht.Range("A" & iCounter).Copy
On Error Resume Next
TgtSht.Range("A" & TgtLastRow + 1).PasteSpecial xlPasteAllExceptBorders
End If
Next iCounter
Application.ScreenUpdating = True
Workbooks(wbSource).Close
End Sub
Now, whilst it does work the first time through, any subsequent run of the code duplicates all values in "A" of SrcSht, whether they exist in TgtSht or not. In other words, the code is simply appending all values in SrcSht where Col C = "F6" and Col W <> "Archive" rather then checking first if that value exists in TgtSht Col A already.
I know it must be a relatively simple fix, but i've been at it a while now and i cant see the wood for the trees and am officially putting out an S.O.S for assistance.