Copy data from one worksheet to another

[LL]

Is there a function or command I can use to get excel to automatically copy a
row from the master worksheet over to a seperate monthly sheet when the data
in a certain cell matches a range of criteria
i.e.
Master (Wksht 1)
A B C
Joe Smith Active 11/01/07
John Doe Closed 11/10/07
Justine A Pending 11/08/07
Kim Al Active 12/07/07

Monthly (Wksht 2)
A B C
=the entire row if cell C is within 11/01/07 - 11/30/07

 

[Max]

Here's one way to get there ..

Assume source data in sheet: Master, cols A to C, data from row1 down
where col C houses the dates (real dates are presumed)

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in any
sheet. It will auto-extract the sheetname implicitly. Technique is Harlan
Grove inspired.

Then in a sheet named: Nov07

Put in A1:
=IF(Master!C1="","",IF(TEXT(Master!C1,"mmmyy")=WSN,ROW(),""))

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW())))
Copy B1 across to D1. Select A11, copy down to the max expected extent of
data in Sheet1. Hide away col A. Format col D as dates to taste. Cols B to D
will return the results sought, ie lines for Nov07, with all results neatly
bunched at the top.

To propagate for other month/yr
Eg: To extract lines for Dec07, just make a copy of Nov07 and rename the
sheet as: Dec07, and you'd get the lines for Dec07. And so on, extend as
desired for each month/yr, easily.

 

[Max]

Typo: Line

.. Select A11, copy down to the max expected extent of data in Sheet1.

should read as:

.. Select A11, copy down to the max expected extent of data in Master.

---

 

[Max]

Apologies, another correction

Lines

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW())))

should read

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(Master!A:A,SMALL($A:$A,ROW())))

(I changed the source sheetname from "Sheet1" to "Master"
halfway through in testing here)

---

 

[LL]

Thanks for the assistance. I will tweak this to the names on the actual
sheet and make it work... Such a time saver!!
THANKS!

 

[Max]

Welcome, LL.