In excel, I am trying to copy a column to another sheet automatica

[Guest]

I want to use a function that will look at a row and if the data is yes then
the corresponding column will be copied to another sheet.

 

[Tom Ogilvy]

Dim rng as Range, cell as Range
set rng = Intersect(activesheet.usedRange, activeCell.EntireRow)
for each cell in rng.Cells
if lcase(cell.Value) = "yes" then
cell.EntireColumn.Copy Destination:= _
worksheets("Sheet2").Columns(cell.column)
end if
Next

 

[Guest]

Thanks,

I am a complete newbie to doing this stuff. Can you tell me which sections
need values to be entered by me?

Thanks in advance,

David

 

[Tom Ogilvy]

as written, it acts on the activecell. There is no provision for entering
data. What data did you want to enter. When you said it had a value of
yes, did you mean it matched some value you furnished?

 

[Guest]

Tom,

I have an excel sheet with columns of information that is data about
different plan options for my clients. I want to be able to put a "yes" or
checkmark or something like this on each column so when it is selected it
copies that information to a separate sheet in the same format. It is as if
I highlight the column and copy and paste the information. there are about
75 columns so it is easier for us in our worksite to scroll across the top of
the columns and check the ones we need, rather than cut and pasting each one.
Perhaps a way of keying each column with a code like Access uses could make
it easier. In that scenario, I would type the key code into a cell and the
desired column would appear. Any suggestions are highly appreciated.

Thanks in Advance,

David

 

[Tom Ogilvy]

That is what it does. Since you specified yes, it looks for yes. If you
want something else, then change the yes to the something else.

It copies the entire column and puts it in the same column location on the
sheet2. You didn't specify where you wanted it.