Activate/Select Sheet Error

[bvanscoy678]

Hello,


I am getting an error with my code and I am not sure why. I think it is because the sheet is not active, but unclear.


Error happens with this line: ApprovedSheet.Cells(2, 5).Select


The "Import" worksheet is the active worksheet at the time

Thanks,

Brent

[pre]

Option Explicit
Sub SortApprovedRejected()

Dim ApprovedSheet As Worksheet
Dim ImportedSheet As Worksheet
Dim RejectedSheet As Worksheet
Dim PendingSheet  As Worksheet
Dim iCol As Long, iRow As Long, iRowStart As Long, iRowEnd As Long, iNextRow As Long
Dim UniqueIDApprovedWorksheetRng As Range
Dim UniqueIDRejetectedWorksheetRng As Range

Set ApprovedSheet = ThisWorkbook.Worksheets("Approved")
Set ImportedSheet = ThisWorkbook.Worksheets("Imported")
Set RejectedSheet = ThisWorkbook.Worksheets("Rejected")
Set PendingSheet = ThisWorkbook.Worksheets("Pending")

PendingSheet.Cells.Clear

'//////// My ApprovedWorksheet has an extra column, so this add's the extra column prior to copying
ImportedSheet.Columns(5).Insert
ImportedSheet.Columns(10).Insert
'//////// This will be for my counter
iRowStart = 2
iRowEnd = ImportedSheet.Cells(Rows.Count, "A").End(xlUp).Row ' Find the last row of data
iCol = 20

For iRow = iRowStart To iRowEnd

If ImportedSheet.Cells(iRow, iCol).Value = "Approved" Then
'/// move to appropriate sheet
If Application.CountIf(ApprovedSheet.[a:a], ImportedSheet.Cells(iRow, 1)) = 0 Then
iNextRow = ApprovedSheet.Cells(ApprovedSheet.Rows.Count, 1).End(xlUp).Row + 1
ApprovedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
End If
ElseIf ImportedSheet.Cells(iRow, iCol).Value = "Rejected" Then
If Application.CountIf(RejectedSheet.[a:a], ImportedSheet.Cells(iRow, 1)) = 0 Then
iNextRow = RejectedSheet.Cells(RejectedSheet.Rows.Count, 1).End(xlUp).Row + 1
RejectedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
End If
Else: ImportedSheet.Cells(iRow, iCol).Value = ""
If Application.CountIf(PendingSheet.[a:a], ImportedSheet.Cells(iRow, 1)) = 0 Then
iNextRow = PendingSheet.Cells(PendingSheet.Rows.Count, 1).End(xlUp).Row + 1
PendingSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
End If

End If
Next iRow

ImportedSheet.Cells.Clear

ApprovedSheet.Cells(2, 5).Select
ActiveCell.FormulaR1C1 = "=IF(RC4<>"""",RC3&"", ""&RC4,"""")"
ApprovedSheet.Range("E2").AutoFill Destination:=Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)

End Sub

[/pre]

 

[Debraj]

Hi Brent,


Change this line

ApprovedSheet.Cells(2, 5).Select


to this

[pre]ApprovedSheet.Select
Cells(2, 5).Select

[/pre]
Regards,

Deb

 

[bvanscoy678]

That fixed it. Thank you very much. Brent