Loop issues - Find value in range that fits value in defined name range

I have a ton of data to filter and copy out to new workbooks based on a portfolio code.

I am attempting to loop through "rng" which is the unique numbers from the data, and I am looking it up in "wsd" which is the raw data sheets colum E which holds each lines portfolio code.

This is my first attempt at a loop with vba and I'm having an issue with looking up a range and referencing it against another range. The below code is giving me a runtime error 13 "Type Mismatch" message but I can't figure out why. I have a few ideas but none I can work out how to create a fix. Any suggestions as always welcomed

Dim spath As String
Dim wb2 As Workbook
Dim wsd As Range, rng As Range

Sub Portfolio()

Application.ScreenUpdating = False

Set rng = Sheets("Stats").Range("K2:K3")
Set wsd = Sheets("Data").Range("E:E")
spath = "K:\Project Rainbow 3\08. Design\00. Nov2012 Mobilisation\02. FO & C\02 - Project Documents\2013 Branch & Directs RSK 340\02. PROJECT DOCUMENTS\Bank Manager\Private Banking\PF_Cleanse\"


'   Start Loop
For Each row In rng

'   Searches for portfolio number in data and copies line if found
    If wsd = rng Then        Selection.End(xlToLeft).Select
            Selection.Copy
    End If
    
    '   Need to insert if rng value not found in wds then next row else keep running code

'   Pastes values in new workbook
Set wb2 = Workbooks.Add
        With wb2
    Sheets("Sheet1").Range("A:A").End (xlUp) - 1
        .Value2 = .Value2
        End With

    wb2.Close
    wb2.SaveAs Filename:=spath & rng & ".xlsx"


Next row

Application.ScreenUpdating = True

End Sub

At the moment I am testing but when done K2:K3 becomes "portf" which is a defined name range of 200 odd lines in the G column of sheets "Stats"

Hi,

try this approach of using ado to extract based on a sql statement.

Hi,

You cannot compare two range like this. You need to check cells wise. You can do something like below:

For Each cell1 In range1  
        For Each cell2 In range2  
            If cell1 = cell2 Then  
               '' DO what you want here
            End If  
        Next y  
    Next x

Regards,
Paresh J

Yes, exactly it runs the same way as you said. And my apologies, it should be

cell1 = cell2

Regards,
Paresh J

What exactly you want to paste. Is it a range of cells or single cell?

As your code has changed, you need to modify this part too.

What you need to do is get the union of range of cells when your are in for loop and select that range for copy and the your further code will work.

Regards,
Paresh J