Looping through a named range to find corresponding value

[joanna_sjw]

Hello! I'm kinda stuck with my current VBA code. I have 2 workbooks, one contains the raw data that I want to copy from and the other to paste to.
So there is some complications because wb1 data is in a dynamic alignment for me to use copy and paste function. So I tried to use some variables to determine the row and column of the values I want to copy from by looping through a named range.
Stall A, B, C names are fixed in alphabetical order, BUT if one closes on a specific day, e.g. Stall A, Stall A does not appear in the data with $0. It will simply be replaced by Stall B in the same alignment. But I am doing a database compilation, hence I will need to see Stall A as $0.

I created a list of outletnames as a named range. And below is my current code. Two questions:
1. It is getting stuck here "With ws1.Range("A" & netrevenuerow1, "ZZ" & netrevenuerow1)". But I can't seem to find out why, error shown is "'Range of object'_Worksheet failed"
2. I have not tried the looping since it hasn't gone pass problem 1, does it seem workable?

Sub Compile()

Dim wb1                     As Workbook
Dim ws1                     As Worksheet
Dim mainsheet               As Worksheet
Dim datalastrow             As Long
Dim netrevenuerow1          As Long
Dim rFind                   As Range
Dim Routing_Column          As String

Set wb1 = Workbooks.Open(ThisWorkbook.Worksheets("Automation").Range("B5"))
Set ws1 = wb1.Worksheets("SalesSummary")
Set mainsheet = ThisWorkbook.Worksheets("Database")

datalastrow = mainsheet.Cells(Rows.Count, "I").End(xlUp).Row + 1
netrevenuerow1 = Application.WorksheetFunction.Match("NET REVENUE", ws1.Range("D1:D100"), 0) + 1

'NET REVENUE
'Copy and Paste Food Data
Dim o As Range
Dim oName As Range

Set oName = ThisWorkbook.Worksheets("Automation").Range("Outlets")

With ws1.Range("A" & netrevenuerow1, "ZZ" & netrevenuerow1)
For Each o In oName
    Set rFind = .Find(What:=o, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
        Routing_Column = Split(rFind.Address, "$")(1) & netrevenuerow1 + 1
        ws1.Range(Routing_Column).Copy
        datalastrow = mainsheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
        mainsheet.Range("b" & datalastrow).PasteSpecial Paste:=xlPasteValues
        End If
    
Next o
End With
End Sub

Example mock data.

 

[davesexcel]

netrevenuerow1 = Application.WorksheetFunction.Match("NET REVENUE", ws1.Range("D1:D100"), 0) + 1

What does netrevenuerow1 equal? Your image shows "NET REVENUE" in column G

 

[joanna_sjw]

What does netrevenuerow1 equal? Your image shows "NET REVENUE" in column G

Hello! thanks for noticing, the net revenue is actually in a merged cell (from raw data), so it starts from column D, which was why I put column D in the code. netrevenuerow1 gives 63.