VBA Copy and Paste LOOP

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

For Each dd1 In Range("A:A").SpecialCells(xlConstants)
     
      Sheets("OC1").Select
      dd1.Select
     
    Rows("2:2").Copy
    Rows("1:1").Select
    ActiveSheet.Paste
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
 
Next dd1

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?

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

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.

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!

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.

Sub A()

Dim dd1 As Range

For Each dd1 In Range("A:A").SpecialCells(xlConstants)
    
      Sheets("Sheet1").Select
      dd1.Select
      
    Range("A1").Select
    If ActiveCell.Offset(1, 0) = "" Then
        Exit Sub
    Else
        Rows("2:2").Copy
        Rows("1:1").Select
        ActiveSheet.Paste
        Rows("2:2").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlUp
    End If
    
Next dd1

End Sub

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.

Sub A()

Dim i As Integer
i = 0

Do Until i = 1000000000 'Or any other random large number that will never be met
      
    Range("A1").Select
    If ActiveCell.Offset(1, 0) = "" Then
        Exit Sub
    Else
        Rows("2:2").Copy
        Rows("1:1").Select
        ActiveSheet.Paste
        Rows("2:2").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlUp
    End If
    
    Loop

End Sub

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!

   Sheets("FrontEndApp").Select
         Worksheets("OC").ExportAsFixedFormat Type:=xlTypePDF, _
         Filename:=Range("i23").Value, _
         Quality:=xlQualityStandard, _
         IncludeDocProperties:=True, _
         IgnorePrintAreas:=False, _
         OpenAfterPublish:=False

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

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

Sub A()

Dim i As Integer
i = 0

Do Until i = 1000000000 'Or any other random large number that will never be met
      
    Range("A1").Select
    If ActiveCell.Offset(1, 0) = "" Then
        Exit Sub
    Else
        Rows("2:2").Copy
        Rows("1:1").Select
        ActiveSheet.Paste
        Rows("2:2").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlUp


   Sheets("FrontEndApp").Select
         Worksheets("OC").ExportAsFixedFormat Type:=xlTypePDF, _
         Filename:=Range("i23").Value, _
         Quality:=xlQualityStandard, _
         IncludeDocProperties:=True, _
         IgnorePrintAreas:=False, _
         OpenAfterPublish:=False
    End If
    
    Loop

End Sub

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.

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