Copy-Paste while looping
 

I'm having trouble getting this one to work.
Sheet1 is collecting summary information.
Sheet2 contains 2 columns of data - D and E. Column D contains the
relavent data, column E contains a TRUE/FALSE flag to indicate if the
data is required.

I need to create a macro on Sheet1, that checks Sheet2 Column E5, if
TRUE copy D5 to Sheet1 C17, LOOP to E6, if FALSE LOOP to E6.

Continue until next loop is empty.

Eg
Sheet2
D5 Tom E5 FALSE
D6 Dick E6 FALSE
D7 Harry E7 TRUE
D8 Sam E8 TRUE
D9 Jack E9 FALSE
D10 Mary E10 TRUE
D11 Sue E11 FALSE

Would give
Sheet1
C17 Harry
C18 Sam
C19 Mary

This created list on Sheet1 needs to be continuous, without any spaces
that may be caused by FALSE's.

Macro would continue to loop checking for sheet 2 TRUE's, making a list
in Sheet1 until there is no more TRUE of FALSE in Column E on Sheet2.

Note, the length of Sheet2 column E is fixed, so a Range (E5:E155)
could also be used.

Any thoughts on this are appreciated.


---
Message posted from http://www.ExcelForum.com/

Copy-Paste while looping
 

Mike

Try

Sub bbb()
outfile = Sheets("sheet1").Range("c17").Address(external:=Tr ue)
cnta = 0

Sheets("sheet2").Range("e5").Select
While Not IsEmpty(ActiveCell)
If ActiveCell Then
Range(outfile).Offset(cnta, 0).Value = ActiveCell.Offset(0, -1).Value
cnta = cnta + 1
End If
ActiveCell.Offset(1, 0).Select
Wend
End Sub

Tony

----- Mike M wrote: -----

I'm having trouble getting this one to work.
Sheet1 is collecting summary information.
Sheet2 contains 2 columns of data - D and E. Column D contains the
relavent data, column E contains a TRUE/FALSE flag to indicate if the
data is required.

I need to create a macro on Sheet1, that checks Sheet2 Column E5, if
TRUE copy D5 to Sheet1 C17, LOOP to E6, if FALSE LOOP to E6.

Continue until next loop is empty.

Eg
Sheet2
D5 Tom E5 FALSE
D6 Dick E6 FALSE
D7 Harry E7 TRUE
D8 Sam E8 TRUE
D9 Jack E9 FALSE
D10 Mary E10 TRUE
D11 Sue E11 FALSE

Would give
Sheet1
C17 Harry
C18 Sam
C19 Mary

This created list on Sheet1 needs to be continuous, without any spaces
that may be caused by FALSE's.

Macro would continue to loop checking for sheet 2 TRUE's, making a list
in Sheet1 until there is no more TRUE of FALSE in Column E on Sheet2.

Note, the length of Sheet2 column E is fixed, so a Range (E5:E155)
could also be used.

Any thoughts on this are appreciated.


---
Message posted from http://www.ExcelForum.com/

Copy-Paste while looping
 

Thanks for suggestion. However, this gives error
"script out of range".

Debug indicates error at
'outfile = Sheets("Sheet1").Range("c17").Address(external:=Tr ue)'

Any thoughts

--
Message posted from http://www.ExcelForum.com

Copy-Paste while looping
 

Mik

Can't think why this would not be working. I've just created a new workbook with 2 sheets, (sheet1 and sheet2) and run the macro on it and it worked ok

One other thing you could try would be to start the macro on the output sheet and change the code to
outfile = activecell.address(external:=true

You will then have to select the new sheet / range wit
Sheets("sheet2").Activat
Range("e5").Selec

Ton

Copy-Paste while looping
 

Tony,

Thaks so much for the help. It does in fact work like a charm. My
problem was that the way it was set, I needed to be active on sheet2
for it to work. Trying to start it from sheet1 was giving me the
error.

I will take you latest suggestion and try the sligh change, as I would
rather be on sheet1 (output sheet) when running this code.

Thanks again.

Mike


---
Message posted from http://www.ExcelForum.com/