VBA Copy and Paste LOOP

[rlsublime]

I am trying to copy row 2, paste it into Row 1 and delete row 2 and then loop through col A:A to look for a nonblank value and then keep repeating the code till it only Row one is left and all the other rows are deleted. Currently it is doing this partially in that it is only getting to half way through the list and then ending. For ex: In a list with 65 records, it will run the code for 31 records and then end. Any idea how I could solve this issue. Thanks

Please Login or Register  to view this content.

[majosum]

After you copy and paste row 2 to row 1, why don't you just have the macro delete all rows except for row 1?

[rlsublime]

Because in row 1, I am performing additional routines that will link values in row one to another sheet and then print that sheet, which is why the loop is critical. I am not sure why it is currently getting down to halfway on the list and then ending. Thanks

[rlsublime]

Hello: Here is the sample file of what i am trying to do. The routine will run about half way and will stop. I am trying to get it to loop through all values and then stop after the last one. also, I want row one to remain there and not be deleted. Any idea how I could do this. Im sure I am missing something simple but I cannot figure it out. Thanks in advance for your help.

[majosum]

Sorry, I am on a Mac for the night and cannot view macros. If you can copy the code, I could try to take a crack at it or else you will have to wait until I get into work tomorrow.

Or if anyone else can help, please do!

[majosum]

I just took a quick look at it and I think this works to stop it on the last row. However, I could not figure out why it would randomly stop. When I would step into the macro, it worked perfectly, but if I clicked the play button, it would have the same problem you mentioned.

Hopefully this helps some.

Please Login or Register  to view this content.

[majosum]

Got it! Just use a loop that will count a long time and make sure it is much larger than the number of rows you will have. In the IF statement, the sub will exit once you encounter a blank, so the large number won't matter.

Please Login or Register  to view this content.

[rlsublime]

thank you that works. I am also trying to add the following piece to this loop. This piece will perform the code and print in PDF form and then move onto the next. Any idea where in the above code I would need to place this for it to loop and print one by one in PDF form. Thank you!

Please Login or Register  to view this content.

[majosum]

What information exactly are you trying to print in PDF form? Just the one line that is left?

[rlsublime]

No, that line is used to populate data in a tab called OC and Range(i23) is the output file which is concatenated and constantly changes so there will never be any overlapping of the PDF files. Currently, when I do the following, it does not work. Any idea how I can alter this code? Thanks

Please Login or Register  to view this content.

[majosum]

I'm not really sure. VBA is not by forte.
The code I provided in the IF statement moves deletes the top row. However, after this is done, you immediately go to another worksheet (FrontEndApp). I don't think your added code is copying/linking the data to the new worksheet.

[rlsublime]

It is linking through formulas to the OC tab, which it does correctly. However, after the copy and paste of the row I am trying to get it to print and then loop. Right now it is printing to pdf only once and then ending. But the copy and paste loop works perfectly. For eg if there are 20 values in A:A, only the first value will print to pdf but then all the values will loop through the copy and paste. Thanks