Janice
If you wish, we can do this with email. My email address is
. Remove the "extra" from this address. Otto
"Doehead" wrote in message
...
Thanks for your response Otto, I have given this information to the gal so
she can make the decision on what columns to show on each sheet and what
event she wants to trigger updating the sheets (she has already changed
her
mine on the Women's Health sheet). I will respond once she has decided.

Until then thanks for all your help, I have learned a lot in this past
week.
Janice

"Otto Moehrbach" wrote:

Janice

You say:

Also I am curious from my searches........ to leave basically all

formatting as 'general', which I have except for date field. Why is that?

I don't know what you were reading. Basically, you should format
whatever
cells/columns/rows/sheets the way you want. Only if you don't have a
specific format in mind should you leave it in General.



You say:

.. I have tested the workbook and unless you run the macro every time it
does

not automatically update the Women's Health sheet. Can we automate it?
(if

you think am slow, wait til I have to explain to the end users or the gal

what they have to do to see updated info in the spreadsheets!).

Yes, you can automate it, but you must tie the running of the macro to
some
event that Excel recognizes. Excel recognizes many events. Some
examples
a

Opening the file

Closing the file

Saving the file

Printing something from the file

Selecting a cell

Making a cell entry

Selecting a sheet

Many more

Excel will do anything you want (run the macro) when an event of your
choosing occurs. So the ball is back in your court: When do you want
this
macro to run?



You say:

I am assuming that I can use your macro (with minor changes to column

names) to create the same results in Women's Health for the other
worksheets

correct?

Yes, you can, as long as you make the necessary changes. But why not
make
Excel do the work for you? I can write the macro to do all the sheets at
one time. If you want this, are the columns to be copied the same in
each
case? If not, what are the columns for each sheet? What are the other
sheet names? Otto

"Doehead" wrote in message
...
Thanks Gary I did it!!!!! With a bit of searching on 'how to'.

Otto, I have a few more questions before I leave you alone:

What this gal is trying to do is enter all information on a Master
sheet.
From there she wanted me to create separate sheets for other end users
(basically viewing and printing purposes only) plus a "closed" sheet;
that
way she can autofilter on "Open" and only see in the Master what is
outstanding (I have done the closed sheet using the IF statement).

1. I am assuming that I can use your macro (with minor changes to
column
names) to create the same results in Women's Health for the other
worksheets
correct?

2. I have tested the workbook and unless you run the macro everytime it
does
not automatically update the Women's Health sheet. Can we automate it?
(if
you think am slow, wait til I have to explain to the end users or the
gal
what they have to do to see updated info in the spreadsheets!).

3. Also I am curious from my searches........ to leave basically all
formating as 'general', which I have except for date field. Why is
that?

Thanks again for your assistance. Janice

"Otto Moehrbach" wrote:

Gord is right. Come back if you need more. Otto
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Otto's macro should be stored in a general module and is run
manually
by
ToolsMacroMacros. Select and run.

It is not event code so won't run automatically.


Gord Dibben MS Excel MVP

On Tue, 14 Oct 2008 20:01:00 -0700, Doehead
wrote:

Hi Otto,

I right mouse clicked on the Women's Health tab / clicked view code
/
pasted
your calculation / closed the workbook / reopened and enabled macros
when
asked...I am assuming the macro ran. Also I am using Excel 2003.
Did I
do
it
right? Janice

"Otto Moehrbach" wrote:

What do you mean when you say "copied the macro under sheet
Women's
Health"?
What exactly did you do? Give me a step-by-step. Did you run the
macro?
Otto
"Doehead" wrote in message
...
Hi Otto, thank you for all your work on this Macro but it is not
doing
anything. I copied the macro under sheet Women's Health
nothing...
deleted
then copied under sheet Master nothing...... then deleted and
copied
under
the workbook. Is it because on the Master sheet column A has a
unique
record
number that I did not want to copy over to Women's Health? I did
change
the
macro columns as I was off one column after I added the URN
column(A):

Sub CopyYes()
Dim rRowRng As Range
Dim rColZ As Range
Dim i As Range
Dim Dest As Range
Application.ScreenUpdating = False
Sheets("Master").Select
Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1")
Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp))
With Sheets("Women's Health")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
For Each i In rColZ
If UCase(i.Value) = "YES" Then
rRowRng.Offset(i.Row - 1).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
Application.ScreenUpdating = True
End Sub

Thanks for the website .......... looks scary but will start to
look
through
it. Janice

"Otto Moehrbach" wrote:

Here is a macro to do what you want. This macro is just for
the
"Women's
Health" sheet and for the condition of column Y being "Yes".
Note
that
your
file must have a sheet named "Master" and another sheet named
"Women's
Health".
You said that you need to do 5 other sheets using different
criteria.
That
can all be done with just one macro. Tell me what the sheet
names
are
and
what the criteria is. Do you want the same columns copied or
does
that
change also? What version of Excel are you using?
You said that you don't know macros (VBA).
David McRitchie has some notes for getting started with macros
at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Sub CopyYes()
Dim rRowRng As Range
Dim rColY As Range
Dim i As Range
Dim Dest As Range
Application.ScreenUpdating = False
Sheets("Master").Select
Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1")
Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp))
With Sheets("Women's Health")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
For Each i In rColY
If UCase(i.Value) = "YES" Then
rRowRng.Offset(i.Row - 1).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
Application.ScreenUpdating = True
End Sub

"Doehead" wrote in message
...
Hi Otto, thanks for replying, I would want to paste them in
sequential
columns on the new spreadsheet.

"Otto Moehrbach" wrote:

Let me explain what I mean. Let's say you want to copy 15
columns, or
30
columns, or 50, it doesn't matter. They are not sequential
columns,
maybe
some are, and they are scattered all over the place, just
like
your
source
columns are. Copying those columns is easy in VBA. OK,
we're
done
with
copying. Now let's talk about pasting all this data we just
copied.
This
is the question I have. A pasting question. Do you want to
paste
these
umpteen columns of data into sequential columns? That is
easy
in
VBA.
Or
do you want them pasted into non-sequential columns? If so,
what
columns?
HTH Otto
"Doehead" wrote in
message
...

Hi Otto,

I want the destination columns to only include specific
source
columns.

Source columns: A through to AU
Destination columns: would start at A but would copy over
B
through
W
plus
AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet.

I have created the destination header row to match the
specific
source
header titles that I need in the destination spreadsheet.

Does this make sense? Thanks, Doehead

"Otto Moehrbach" wrote:

Do you want the destination columns to be the same
columns
as
the
Source
columns or do you want to copy the Source columns then
paste
them
in
sequential columns? Starting with what Destination
column?
HTH
Otto
"Doehead" wrote in
message
...
Hi all,

I am trying to copy a complete row of data to another
sheet
when
it
meets
the criteria:

Source data is in sheet: Master, cols A to AU,