For-Each loop, I think.

[excelnut1954]

Sorry this is so long, but I wanted to lay out what I have and what I
wanted...

From a workbook (Staging List) we update each day, I want to create a

list of records to inventory each day based on what rows they are in.
This list is located in a worksheet named Official List. We have 40
rows in the warehouse, so I have split them up. Certain rows will be
inventoried on Mon. Another set of rows on Tues, And so on each day
thru Friday. So, each day Mon-Fri has a different set of rows to
inventory.

One of the column headings in the worksheet Official List is named
Row. This column is used to show the row number that material is
located.

I've created a table in a worksheet named Cycle Count, which shows
each day Monday thru Friday. And under each day is a list of all the
rows to be counted on that day. I have name the ranges for each day.
All the cells containing the rows to be counted on Monday are named
CycleCount_Monday, for Tuesday, it's CycleCount_Tuesday, etc thru
Friday.

I have a formula that will identify the day of the week for the next
workday. WEEKDAY(NextWorkdate). This formula is in a cell named
DayofWeek. This will return a numeric value of the day of the week of
the next workday (excludes weekends and holidays I have listed). If
the next workday is Monday, the value in that cell will be 2, Tues=3,
Wed=4, Thurs = 5 and Friday = 6.

If, for example, today is Mon (Weekday value 2), and the Monday table
of rows to be counted has R1, R2, and R3, then I want the macro to go
through the worksheet, look in the column under Row, and for each
record that has a row number that is in the Monday table (R1, R2 &
R3), it should copy that record to another workbook (named for this
example Cycle Count.xls. When the macro is done, the other workbook
(Cycle Count.xls) will have a list of all the records that are in R1,
R2 and R3.

It would go something like this: range names are in ( )
If (DayofWeek) = 2 then look at (CycleCount_Monday) (not sure yet
how I will include an If for each day of the week)
Goto (Row)
For each cell under Row that equals any of the rows in
(CycleCount_Monday)
Rows(ActiveCell.Row).Select
Selection.Copy
Windows("Cycle Count.xls").Open
Goto (a named range where it will paste)

I know I probably won't get an exact answer for what I need right
away. But, maybe I can get it started with your help.
Thanks,
J.O.

 

[R1C1]

The best way to start is to record a macro of what you want to do. Then,
post the macro and ask for help with the variables.

Regards,

Alan