For-each to copy rows to another workbook

[excelnut1954]

I received help on this from Tom Ogilvy (which I appreciate, of
course), but I need additional help. I thought I would try to lay out
what I needed differently, as I tend to be too wordy...

This macro should go down a list, and copy rows based on 2 criteria.
1) to determine what the next workday is, like Monday, Tues, etc (a
formula is in place to do this)
2) based on the next workday, it should read a certain table that
contains the warehouse row numbers to look for in the source workbook.

If tomorrow is a Tuesday, then it should look at a table with a range
name of CycleCount_Tuesday, and during the For-each section, copy all
rows from the source list that contain these warehouse row numbers,
and paste them to the target workbook.

Please look at the coding so far. Currently there are no errors given
when I run this. The cursor ends up at the bottom of the source list
like it should, but nothing at all is copied.
My guess is that the assignment is not made during the If-Then-Else
section, so it finds nothing that matches the criteria.
Thanks to all for your help.
J.O.

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

'DayOfWeek is the numeric value of the next weekday based on a
formula.
'CycleCount_Monday (Tues, Wed, etc) are ranges containing the
warehouse rows.
'*****I think this is where the problem is. Assignment value does not
seem
' to be given to RowsToCount.*****
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 the column on the source
list
'to be read by the macro.
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"

'Makes sure cursor starts at the top of the source list.
Range("FirstRowOfficialList").Select
ActiveCell.Offset(1, 0).Select

'Looks at each row for matching criteria based on above IF-Then-Else.
For Each cell In Range("TempColumnName")
If Not RowsToCount Is Nothing Then
If cell.Value = RowsToCount Then
CopyRows 'sub below that will perform copy routine
Else
MsgBox "Nothing copied because " & vbNewLine & _
"Value of Range DayofWeek is " & Range("DayofWeek").Value
End If
End If

ActiveCell.Offset(1, 0).Select

Next cell

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

End Sub

Sub CopyRows()
'Copies rows to target workbook.

Rows(ActiveCell.Row).Select
Selection.Copy

Windows("Inventory Wk of BLANK.xls").Activate
Sheets("Data Dump").Select

Range("B65536").End(xlUp).Offset(1, -1).Select

ActiveSheet.Paste
ThisWorkbook.Activate

End Sub

 

[Tom Ogilvy]

the only help I gave you was to put in a line of code to check if rowstocount
was actually set to something other than nothing.

if Not RowsToCount is nothing then

After I told you that was probably the problem.

You didn't seem to understand what I was saying, but further in the thread
came back and seemed to discover on your own that that appears to be the
problem.

two weeks later, you are saying that again appears to be the problem.


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 code seems straight forward, If Range("DayofWeek) contains a 2, 3, 4,
5, or 6, it should attempt to assign RowsToCount to what I assume is a range
variable such as CycleCount_Monday. I don't see anywhere in your code that
you set CycleCount_Monday or any of the similar variables to refer to a
location anywhere. And in this post, you say they are named ranges. You
can't use a named range directly as a range variable. If the named range is
named "CycleCount_Monday", you would refer to that range as
Range("CycleCount_Monday")

so retaining your declarations of CycleCount_Monday as an object variable
and how you use it in your if statement, You would need to add lines like

set CycleCount_Monday = Range("CycleCount_Monday")
set CycleCount_Tuesday = Range("CycleCount_Tuesday")

before you get to your if statement.

or adjust your if statements


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

End If

Again, this assumes that these are defined names in Insert=>Name=>Define


If you get that working, you will next run into problems with a command like

If cell.Value = RowsToCount Then

Of RowToCount is a multicell range containing warehouse numbers, you would
need something like

if not iserror(applicaton.Match(cell.Value,rowsTocount,0)) then

this assumes that rowstocount refers to only a single column, multirow name
(or single row, multicolumn).


--
Regards,
Tom Ogilvy

 

[excelnut1954]

I've tried different things to eliminate the error I get. It's a 424
Object required error shown below. See the ****************** marker.
Since my VBA projects are a secondary part of my job, I'm able to
dive into it only occasionally. So, since Tom last responded, I've
spent some time trying to learn about what might be causing this
error. I'm stuck. I've tried adding different Dim statements, Set
statements. I don't know what Object the error is referring to. Do I
need to declare the individual sheets as objects? Each workbook?
Any suggestions would be appreciated.
Thanks,
J.O.

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

'DayOfWeek is the numeric value of the next weekday based on a
formula.
'CycleCount_Monday, Tues, etc are ranges containing the warehouse row
'numbers to look for on the list.

With Sheets("Cycle Count")

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

End If
End With

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

'Looks at each row for matching criteria based on above IF-Then-Else.
With Sheets("Official List")
For Each cell In Range("TempColumnName")
If Not RowsToCount Is Nothing Then

'********this is where I get "Runtime error 424 Object
Required" error ***********
If Not IsError(applicaton.Match(cell.Value, RowsToCount, 0))
Then

CopyRows 'sub to perform copy routine
Else
MsgBox "Nothing copied because " & vbNewLine & _
"Value of Range DayofWeek is " & Range("DayofWeek").Value
End If
End If

ActiveCell.Offset(1, 0).Select
Next cell
End With

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

End Sub

 

[Bernie Deitrick]

applicaton

Should be spelled properly....

application

HTH,
Bernie
MS Excel MVP

 

[excelnut1954]

Thanks Bernie.
That was it. I spent more than an hour checking spelling of range
names, etc. I usualy copy commands like this. But, must have hit the
delete key......
Thanks again
J.O.

 

[Bernie Deitrick]

One tip is always type in lower case - if you don't make a mistake, then excel will change the case,
depending on the object name or declaration of a variable (Always use at least one cap in every
variable name).

The other tip is to use autocomplete. When you type

application.

a dialog with properties and methods should show up, so if you typed

applicaton.

no dialog would show up and you would know that you misspelled application.

HTH,
Bernie
MS Excel MVP