Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy, paste without file name referenced after paste | Excel Discussion (Misc queries) | |||
Copy; Paste; Paste Special are disabled | Excel Discussion (Misc queries) | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) |