On Sep 16, 6:24*am, Alexander
wrote:
Hi

I have struggling with one part of my macro.

Basically, I have a row of cells with names. Alex, John, Martha, Jim, etc..
Each person has a list of data under them. I have a master column of data..
Depending who is on duty today, I want the macro to find the person and then
copy paste special the master column over the person column.

Thus, in the macro code where it says

Quote:

Find(What:="John",

I
want it to refer to a specific cell. I.e. what:=$E$4.

Then it must go to the cell it found and copy paste the info in.

Cells.Find(What:="John", After:=ActiveCell, LookIn:=xlFormulas, _
* * * * LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
* * * * MatchCase:=False, SearchFormat:=False).Activate

Any ideas on how to do this?

Regards

Alex

Alex-
The following code is based on a few assumptions. You'll have to
modify these named ranges to meet your needs. Here is how I set up my
worksheet to test:

The row of cells with names I put in row 1, starting in cell D1. The
master list started in cell I1. All their associated values are in
the cells below each name. The cell where you'll input the person's
name on duty is cell A2.

A B C
D E F
G H I
1 On Duty: Alex
John Martha Jim
Master
2 <input name 1
2 3
4 2
3
Temp Full Full
Temp Full



You'll need to establish some named ranges. For the values associated
with each name, create a named range for their list of values. For
example, create a named range called "Alex" that refers to cells
D2:D3. Don't include the field header in the named range. Do this
for all names and also create a named range for the Master list
labeled, "Master". It too should NOT contain the field header (e.g.,
I2:I3 only).

In the module folder of the workbook (Alt + F11) place the following
code:

Sub OnDuty()
Dim rng As Range
Set rng = Range("A2")

Range((rng.Value)).ClearContents
Range("Master").Copy Range((rng.Value))
End Sub

How this works: It sets the "rng" variable equal to the value in cell
A2. This is the name of the person on duty, which also corresponds to
one of the named ranges you want to replace with the values in the
Master named range. Now that you've established the appropriate named
range to replace, the next line clears the contents of that named
range, then copies the master named range into the named range that
matches what's in cell A2.

Hope this works.

Excel.Instructor (Ed2go.com/Advanced Excel)