A quicker way of looping

[Pauljj]

I am using a FOR, NEXT looking, checking through a list of 5,000 names to see if the corresponding cell is populated with any data. if it does it's copying it to another workbook, going back to the original.....etc etc , it seems that doing this check and copying and pasting is taking a substantial amount of time, is there anyway of speeding this up.

This is the main section

For i = 6 To lastrow



    wbo.Activate

    If Cells(i, 265) <> "" Then
    
    Cells(i, 265).Copy
    
    wbn.Activate
    
    newlastrow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Cells(newlastrow + 1, 2).Activate
    
    ActiveCell.PasteSpecial xlPasteValues
    

    wbo.Activate
    
    Cells(i, 112).Copy
    
    wbn.Activate
    
    Cells(newlastrow + 1, 1).Activate
    
    ActiveCell.PasteSpecial xlPasteValues
    
    End If
    
    
Next i

 

[Fluff]

How about

   For i = 6 To LastRow
      With wbo
         If .Cells(i, 265) <> "" Then
            WBn.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Value = .Cells(i, 265).Value
            WBn.Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = .Cells(i, 112).Value
         End If
      End With
   Next i

 

[Pauljj]

Brilliant, thank you very much, from 5 mins to a couple of seconds

 

[Fluff]

As a matter of interest, what is in col 265? Is it hard values, or formulae?

 

[Pauljj]

They are values

 

[Fluff]

In that case here's a non-looping approach

   With wbo.Columns(265)
      .SpecialCells(xlConstants).Copy wbn.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1)
      .SpecialCells(xlConstants).Offset(, -153).Copy wbn.Cells(Rows.Count, 1).End(xlUp).Offset(1)
   End With