How to copy a cell ontent into next available blank cell in sheet2?

[MelissaGregory]

I have 2 worksheets, let's call them "Sheet1" and "sheet2".

Sheet 1 has 2 columns (A, and B)

Ie. "Sheet1:"

Col A, Col B

Red
Green Yes
Blue
Yellow
Orange Yes

For each entry in Column "B" that has a "Yes" value, I need to copy the color value in column "A" into the next available empty cell in "Sheet2" in column A.

"Sheet2"

Col A, Col B

Red 3
Blue 5
Yellow 6


Final Result:

Col A, Col B


Red 3
Blue 5
Yellow 6
Green
Orange

Can someone assist in how to do this particular challenge? For Sheet2, I think I'll need a function to determine the first available blank cell in column A.

Thanks

[alansidman]

Try this:

Please Login or Register  to view this content.

[MelissaGregory]

Thanks Alan!

Your right on the money!!! Your proposed piece of code worked flawlessly!! Thank you so much!!!

Hmm, one other question: if I wanted to clear out all the "Yes" values in "Sheet1", but keep the cell formatting/forumulas of that cell in tact, what piece of code could be ammended to the Sub to remove all "Yes" values? Thanks in advance

Appreciate your help.
Melissa

[alansidman]

Try this:

Please Login or Register  to view this content.

I haven't tested this. It was off the top of my head.

Alan

[MelissaGregory]

Hi Alan,

Thanks again....I think your 2nd code works!! Damn, you're good!

Question though: why didn't you have to declare the worksheet(in this case "Sheet1") via a Dim and Set statement for this particular Sub ams2??

Me

[alansidman]

Because I was only working within the one sheet.

[MelissaGregory]

Hi Alan:

All the above works nicely - thanks again for your generous help

I was wondering for this particular example, if for Column "B" the data values are obtained by VLOOKUP into "Sheet1" using the Column "A" value, what is the VBA syntax to ammend to the code above to copy the VLOOKUP formula from the cell in Column "B" to the copied cell?

I assume that the code would probably need to be inserted after the copy of the value of Column "A" in Sheet "1" into the next free cell in Column "A" in Sheet "2".

w1.Range("A" & i).Copy w2.Range("A" & lr2 + 1)


Thanks!!!
Melissa

[MelissaGregory]

I forgot to mention that the VLOOKUP formulas are of course relative, so the look up paramaters would increment by 1 row for each new appended value.

[alansidman]

I am a little confused. You want to copy the formula in Column B in Sheet 1 to the next empty row in Sheet 2? Perhaps it might help if you explain your project as if we were standing in line in McDonalds. Even better, would be to attach a sample workbook with before and after situations so that we can understand and tailor the response.