在范围内类型不匹配

Question

我正在尝试将过滤范围从主数据集复制到每个国家 /地区的电子表格（循环）。当我在过滤后的工作表中设置范围时，我会收到设置rng1 = ws2.range（“ e2：l”和lrow1）的类型不匹配错误。有人可以帮助我确定不匹配的原因吗？

Sub CopyData_To_TemplateWorkbook2()

Dim wb As Workbook
Dim SavePath, TemplatePath, TemplateFile As String
Dim ws1, ws2, ws3, wbws1, wbws2, wbws3 As Worksheet
Dim rng1, rng2 As Range
Dim MSi As Variant
Dim lRow1, lRow2 As Long

Application.DisplayAlerts = False
'Application.ScreenUpdating = False

TemplatePath = "C:\Users\xyz\Test\"
TemplateFile = "Template_blank.xlsx"

SavePath = "C:\Users\xyz\Test\"

Set ws1 = ThisWorkbook.Sheets("Lists")
Set ws2 = ThisWorkbook.Sheets("Responses 2006 2020")

ws1.Select

For i = 2 To 5 'Loop through list of country names

    Set MSi = ws1.Range("A" & i)

    ws2.Activate
    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If

    ws2.Range("B1").AutoFilter Field:=2, Criteria1:=MSi     'Filter Criteria1 = i
    
    lRow1 = ws2.Range("E" & Rows.Count).End(xlUp).Row

    Set rng1 = ws2.Range("E2:L" And lRow1) 'Type mismatch here

    'Set rng1 = ws2.Range("E2:L") 'Application-defined or object-defined error here if used
    
    Set wb = Workbooks.Open(Filename:=TemplatePath & "Template_blank.xlsx", Editable:=True)

    Set wbws1 = wb.Sheets("Cover sheet")
    Set wbws2 = wb.Sheets("Responses")

    wbws1.Range("B2").Value = MSi
    wbws2.Range("B2").Value = MSi
      
    lRow2 = wbws2.Range("A" & Rows.Count).End(xlUp).Row 'But there is no last row - blank sheet
    
    Set rng2 = wbws2.Range("A6:H") ' And lRow2 ??

    rng2.Value = rng1.Value
    
    wb.SaveAs Filename:=SavePath _
    & MSi & "_text" & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
    wb.Close SaveChanges:=False

    Set rng1 = Nothing


ws1.Select

Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub