I need to download bank activity for many different bank accounts (My code below is only three accounts but the final will be for fifteen) and then move the activity to a separate sheet for each account. I need to modify my code so that if it doesn't find any activity for one bank account it moves on to looking for the next bank account. Currently, when no activity is found for a bank account number I receive a "Run-Time error '91': Object variable or With block variable not set". When I debug it highlights the line "SelectCells.Select". So in the code below, if it didn't find any activity for 2958, it would error at the following "SelectCells.Select". I feel like this is an easy fix that I am just not understanding so hopefully someone here can educate me as to where I went wrong.
VBA Code:
Sub DistributeActivity()
Application.ScreenUpdating = False
Application.Goto Sheets("WF").Range("A1")
'Declare variables
Dim Ws As Worksheet
Dim Ws1 As Worksheet
Dim SelectCells As Range
Dim xCell As Object
Dim Rng As Range
Dim lrw1 As Long
Dim lrw2 As Long
Dim lrw3 As Long
Set Ws1 = Worksheets("WF")
Set Rng = Ws1.Range("F1:F5000")
Set SelectCells = Nothing
lrw1 = Sheets("Land (500)").Cells(Rows.Count, "S").End(xlUp).Row
lrw2 = Sheets("Housing (3480)").Cells(Rows.Count, "S").End(xlUp).Row
lrw3 = Sheets("Stapleton I (2958)").Cells(Rows.Count, "S").End(xlUp).Row
'-------BANK 500-------
'Check each cell in Range("Rng") for bank account value below.
For Each xCell In Rng
If xCell.Value = "#######500" Then
If SelectCells Is Nothing Then
Set SelectCells = Range(xCell.Address)
Else
Set SelectCells = Union(SelectCells, Range(xCell.Address))
End If
End If
Next
'Select the cells with specified value
SelectCells.Select
Selection.Offset(0, -5).Select
Selection.Resize(Selection.Rows.Count + 0, Selection.Columns.Count + 18).Select
Ws1.Names.Add Name:="Land", RefersTo:=Selection
Range("Land").Copy Worksheets("Land (500)").Range("A" & lrw1 + 1)
Set SelectCells = Nothing
'-------BANK 3480-------
'Check each cell in Range("Rng") for bank account value below.
For Each xCell In Rng
If xCell.Value = "######3480" Then
If SelectCells Is Nothing Then
Set SelectCells = Range(xCell.Address)
Else
Set SelectCells = Union(SelectCells, Range(xCell.Address))
End If
End If
Next
'Select the cells with specified value
SelectCells.Select
Selection.Offset(0, -5).Select
Selection.Resize(Selection.Rows.Count + 0, Selection.Columns.Count + 18).Select
Ws1.Names.Add Name:="Hsng", RefersTo:=Selection
Range("Hsng").Copy Worksheets("Housing (3480)").Range("A" & lrw2 + 1)
Set SelectCells = Nothing
'-------BANK 2958-------
'Check each cell in Range("Rng") for bank account value below.
For Each xCell In Rng
If xCell.Value = "######2958" Then
If SelectCells Is Nothing Then
Set SelectCells = Range(xCell.Address)
Else
Set SelectCells = Union(SelectCells, Range(xCell.Address))
End If
End If
Next
'Select the cells with specified value
SelectCells.Select
Selection.Offset(0, -5).Select
Selection.Resize(Selection.Rows.Count + 0, Selection.Columns.Count + 18).Select
Ws1.Names.Add Name:="CPI", RefersTo:=Selection
Range("CPI").Copy Worksheets("Stapleton I (2958)").Range("A" & lrw3 + 1)
Set SelectCells = Nothing
Ws1.Cells.ClearContents
'Select Cell A1 on each worksheet.
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Visible Then
Ws.Activate
Range("A1").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End If
Next Ws
Application.ScreenUpdating = True
End Sub
