Shorter way to copy/paste between sheets

  • Hello

    I have a sheet with approximately 1200 rows of data.

    Currently, the only way for me to copy rows to different sheets is to select each row (based on specific criteria (column value)).

    It all works, but it takes a while to run - because I am iterating through each of the 1200 rows, finding a value from the O column, finding the appropriate sheet to paste it into, and then pasting the entire row of data into the next available row.

    So, what Im doing is looking through each cell in the O column, grabbing the value, and comparing it to the sheet names (the sheetnames were created based on the unique values from the O column). When it finds the sheet name with the same value as the cell, it pastes the row into the next available row.

    But it does this for 28 different sheets (potentially). so it takes approx 30,000 searches/copies/pastes.


    the O column contains 50 occurances of "Community Services", 65 occurences of "Corporate Services" etc for 1200 rows of data.

    I have sheets by those two names (Community Services and Corporate Services).

    Is there a way to have vba scan through all 1200 rows and select/copy all the appropriate rows, then do 1 paste into the appropriate sheet?

    Thank You


  • Alternatively you could try a loop like:

    Sub copyRWs()
    Application.ScreenUpdating = False
    Dim a
    a = Application.Transpose(Sheet1.Range("O2:O" & Sheet1.Cells(Rows.Count, "O").End(xlUp).Row)) 'creates array from O2:last row
    For i = LBound(a) To UBound(a)
    Sheet1.Rows(i + 1).EntireRow.copy (Sheets(a(i)).Cells(Sheets(a(i)).Cells(Rows.Count, 14).End(xlUp).Row + 1, 1)) 'copies each row
    Next i
    Application.ScreenUpdating = True
    End Sub

    This assumes you have a table starting on row 1, change sheet1 to your sheetname , if jolivanes nor my response achieve what you are after an example worksheet is probably needed.

  • Hello Terry,

    We could take both suggestions from Jolivanes and Justin one step further.

    To speed things up a little more, extracting the unique names first from Column O, placing them in a temporary helper column, sorting them Ascending then placing them into an array will reduce iterations considerably. The code I have in mind is as follows:-

    I've randomly selected Column Z as the helper column. You can use any column you wish well away from your data set.

    You will be able to add further worksheets as required. The code will take care of the rest.

    I've attached a sample workbook to show you how this works. Click on the "TEST" button.

    I hope that this helps.



    Terry Test.xlsm

  • This is untested.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!