Error 1004 ‘Range’ of object ‘_Worksheet’ failed when trying to copy values (Workbook and worksheet explicitly set, no named ranges)
My problem is stated in the title. The error occurs in the first line with .Copy, but I have had it the same as the second one and received the same error.
I have checked so that the Sheet names are correct, and even copied them straight from the Sheet title in case some weird character sneaked in.
I’ll put snippets of code here and then the full code in the end in case the problem is something different.
Declaration:
(I have tried setting it explicitly with Workbooks() but it didn’t help)
Dim wb As Workbook
Set wb = ThisWorkbook' Or Workbooks("collected.xlsm")
Dim sUser As Worksheet, sExceptions As Worksheet
Set sUser = wb.Sheets("User")
Set sExceptions = wb.Sheets("Exceptions")
Copying:
sUser.Range(Cells(rS, 1)).Copy Destination:=sExceptions.Range(Cells(Count, 1))
sUser.Range(rS, 11).Copy Destination:=sExceptions.Range(Count, 2)
Entire code:
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
1 Answer
1
You’re confusing Range and Cells.
Range
Cells
Try
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)
By clicking “Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Website Information 
– Rawrplus
Jul 3 at 9:16