Looping limitation - Help on Copying Rows to New File

[diadromous]

Hello -

I have a database that needs breaking down in order to fulfill a request.

I've been trying to create a macro to copy one row to a new workbook (starting from row 3), file name save as a value of the cell (C1), and move onto the next row. However, my code appears to only loop through 26 entries and then stops.

 Dim row As Long
    Dim refname
    
    row = 3
    Do While Cells(row).Value <> ""
    Rows(row).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
      
    DirPath = MyPath
    refname = ActiveSheet.Range("C1")
    ActiveWorkbook.SaveAs DirPath & refname
    ActiveWindow.Close

    row = row + 1
    
    Loop

Is there something I am doing wrong? Any help would be a HUGE help!

 

[farmerscott]

Welcome to the forum........

What do you have (or not have) in row 27?

Try changing your code to-

Sub copy()
 Dim refname
Dim x as range
Dim lr as long
    

lr = Worksheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row

For x= 3 to lr
   
    if Cells(x,3).Value <> ""
    Rows(x).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
      
    DirPath = MyPath
    refname = ActiveSheet.Range("C1")
    ActiveWorkbook.SaveAs DirPath & refname
    ActiveWindow.Close

   Next x

End Sub

FarmerScott

 

[diadromous]

Thanks for the feedback farmerscott! I've reviewed the database, but can't seem to find what is in row 27. From what I can tell, all the data seems to be uniform.

However, I finally figured out through trial and error that if I add a space in between the quotes

Do While Cells(row).Value <> " "

it runs beyond the said row.

I guess this is a rookie mistake.

But a huge thanks for helping out - your code is much more of an elegant solution than mine, and already understand a bit more about VBA by looking at what you did!

 

[farmerscott]

Diadromous,

Based on your changing the <>"" to <>"[space] ".....

Where has your database come from? Has it been imported from somewhere?
Often imported data can have spaces in the cells.
Do a test on the cell in row 27 using =ISBLANK(cell), and see if it comes up with true or false.

I am no VBA expert, I am just learning as I go, but by all means have a look at the codes written on this forum to get ideas on how the experts go about it. VBA has some great flexibility to go about the same problem from different solutions.

Hope that helps,

FarmerScott