This macro will copy rows from one workbook to another based on what
warehouse location (row) they are in.

Near the bottom, you'll see where I note where I'm getting an error.
I'm sure it's something to do with either the Dim statements, or maybe
the syntax of the line I'm getting the error in.The error is "Object
variable or With block variable not set".
CycleCount_Monday, Tues, Wed, etc are range names containing the
warehouse rows to count that day.
DayOfWeek is range that will show the value of the next workday.
Mon=2, Tues=3, Wed=4, etc.

Sub CycleCount()

Dim RowsToCount As Range
Dim CycleCount_Monday As Object
Dim CycleCount_Tuesday As Object
Dim CycleCount_Wednesday As Object
Dim CycleCount_Thursday As Object
Dim CycleCount_Friday As Object
Dim DayOfWeek As Integer

'Opens up the Blank inventory workbook (target)
ChDir "S:\Furniture Staging List\Staging List Inventories"
Workbooks.Open Filename:= _
"S:\Furniture Staging List\Staging List Inventories\Inventory
Wk of BLANK.xls"

'Switch back to original (source) file
ThisWorkbook.Activate
Worksheets("Cycle Count").Activate

If Range("DayOfWeek") = 2 Then
Set RowsToCount = CycleCount_Monday
ElseIf Range("DayOfWeek") = 3 Then
Set RowsToCount = CycleCount_Tuesday
ElseIf Range("DayOfWeek") = 4 Then
Set RowsToCount = CycleCount_Wednesday
ElseIf Range("DayOfWeek") = 5 Then
Set RowsToCount = CycleCount_Thursday
ElseIf Range("DayOfWeek") = 6 Then
Set RowsToCount = CycleCount_Friday

End If


'This sets the range name TempColumnName to 1st Row in Official List
Worksheets("Official List").Activate
Application.Goto Reference:="FirstRowOfficialList"
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="TempColumnName", RefersToR1C1:= _
"='Official List'!R6C3:R416C3"

Range("FirstRowOfficialList").Select
ActiveCell.Offset(1, 0).Select

For Each cell In Range("TempColumnName")
'''If cell.Value = "R10" Then 'this worked when I plugged in
one of the row numbers instead of a variable as in next line.
If cell.Value = RowsToCount Then 'THIS IS WHERE I'M GETTING
ERROR

CopyRows 'sub to perform copy routine

End If

ActiveCell.Offset(1, 0).Select

Next cell

ThisWorkbook.Activate
ActiveWorkbook.Names("TempColumnName").Delete

End Sub

Any suggestions would be appreciated.
Thanks,
J.O.