vba - 尝试复制值时对象“_Worksheet”的错误 1004“范围”失败（明确设置工作簿和工作表，没有命名范围）

问题描述

我的问题在标题中说明。错误发生在 .Copy 的第一行，但我已经将它与第二行相同并收到相同的错误。

我检查了工作表名称是否正确，甚至直接从工作表标题中复制它们，以防一些奇怪的角色潜入。

我将在此处放置代码片段，然后在最后放置完整代码，以防问题有所不同。

声明：（我尝试使用 Workbooks() 明确设置它但没有帮助）将 wb 作为工作簿进行调暗

Set wb = ThisWorkbook' Or  Workbooks("collected.xlsm")
Dim sUser As Worksheet, sExceptions As Worksheet
Set sUser = wb.Sheets("User")
Set sExceptions = wb.Sheets("Exceptions")

复制：

sUser.Range(Cells(rS, 1)).Copy Destination:=sExceptions.Range(Cells(Count, 1))
sUser.Range(rS, 11).Copy Destination:=sExceptions.Range(Count, 2)

完整代码：

Option Explicit

Function FindExceptions()

    ' To run faster
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' Variable def
    Dim Count As Integer

    ' Variable def
    ' Worksheets
    Dim wb As Workbook
    Set wb = ThisWorkbook ' Or Workbooks("collected.xlsm")
    Dim sUser As Worksheet, sVCD As Worksheet, sFullExport As Worksheet
    Set sUser = wb.Sheets("User")
    Set sVCD = wb.Sheets("VCD")
    Set sFullExport = wb.Sheets("FullExport")
    ' r, f, c = Search, Find, Check
    ' For Each rows
    Dim rS As Integer, rF As Integer, rC As Integer
    'Set rS = sUser.Columns("A")
    'Set rF = sVCD.Columns("A")
    'Set rC = sFullExport("B")
    ' Vars used in execution
    'Dim cS As Range, cF As Range, cC As Range
    Dim secId As String, employeeNum As String, FoundVCD As Boolean, FoundFullExport As Boolean


    ' Go through User sheet
    For rS = 2 To sUser.UsedRange.Rows.Count
        secId = sUser.Cells(rS, "A").Value
        employeeNum = sUser.Cells(rS, "K").Value
        ' Search for in VCD
        FoundVCD = False
        For rF = 2 To sVCD.UsedRange.Rows.Count
            If sVCD.Cells(rF, "A").Value = secId And sVCD.Cells(rF, "K").Value = employeeNum Then
                FoundVCD = True
                Exit For
            End If
        Next
        'Search for in Full Export?
        If FoundVCD = True Then
            FoundFullExport = False
            For rC = 2 To sFullExport.UsedRange.Rows.Count
                If sFullExport.Cells(rC, "B").Value = secId Then
                    FoundFullExport = True
                    Exit For
                End If
            Next
        End If

        If FoundFullExport = False Then
            ' WriteExceptions sUser.Cells(rS, "A").Value, sUser.Cells(rS, "K").Value, sFullExport.Cells(rC, "A").Value, sFullExport.Cells(rC, "D").Value

            ' Worksheet var
            Dim sExceptions As Worksheet
            Set sExceptions = wb.Sheets("Exceptions")

            If Count = Null Or Count = 0 Then
                sExceptions.Cells(1, "A") = "Säk. Id"
                sExceptions.Cells(1, "B") = "Anst. Nr"
                sExceptions.Cells(1, "C") = "Unison Id"
                sExceptions.Cells(1, "D") = "Kort hex"
                Count = 2
            Else
                Count = Count + 1
            End If

            ' secId on col A, employeeNum on col B, unisonId on col C, cardHex on col D
            sUser.Range(Cells(rS, 1)).Copy _
                Destination:=sExceptions.Range(Cells(Count, 1))
            sUser.Range(rS, 11).Copy _
                Destination:=sExceptions.Range(Count, 2)
            sFullExport.Range(rC, 1).Copy _
                Destination:=sExceptions.Range(Count, 3)
            sFullExport.Range(rC, 4).Copy _
                Destination:=sExceptions.Range(Count, 4)
        End If

    Next

    Count = 0

    ' To end settings to run faster
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Function

标签： vbaexcelexcel-2013

sUser.Cells(rs, 1).Copy _ Destination:=sExceptions.Cells(count, 1) sUser.Cells(rs, 11).Copy _ Destination:=sExceptions.Cells(count, 2) sFullExport.Cells(rC, 1).Copy _ Destination:=sExceptions.Cells(count, 3) sFullExport.Cells(rC, 4).Copy _ Destination:=sExceptions.Cells(count, 4)

vba - 尝试复制值时对象“_Worksheet”的错误 1004“范围”失败（明确设置工作簿和工作表，没有命名范围）

问题描述

解决方案

推荐阅读