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,
data from row 2 down, with key col X & criteria value: Closed

On new sheet: Closed
Placed in A2:
=IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),""))
Left A1 blank

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1))))

This formula has worked for me in the past for another workbook
when I
have
had to copy over a complete row to another sheet.

Bigger problem..I have been asked to copy only certain columns to
a
different sheet in the same workbook.

Again source data is in sheet: Master, cols A to AU
Data from row 2 down, with key col Y & criteria value: Yes

On sheet: Women's Health
Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and
AT
when
the
column Y criteria is Yes.

There is actually 5 other sheets they want me to create using
different
criteria, can Excel do this?

I do not how to do macros or VB, please help. Thanks