Results 1 to 6 of 6

Thread: [RESOLVED] Speed up the copying of data from one worksheet to another

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,310

    Resolved [RESOLVED] Speed up the copying of data from one worksheet to another

    In Excel have this code which copies numbers from one worksheet to another. The destination column to which the data is copied depends on the value of the number. For example 1s are copied to column C, 2s to D, 5s to G, 18s to T, etc. The “- 2” is there because the source worksheet has two heading rows and the destination worksheet doesn’t have any.

    Code:
    For lngRow = 3 To lngLastRow
            wsKD.Cells(lngRow - 2, "A") = wsManual.Cells(lngRow, "A")
            wsKD.Cells(lngRow - 2, "B") = wsManual.Cells(lngRow, "B")
            For lngCol = 3 To 22 ' These are the Manual Draws columns
                intNum = wsManual.Cells(lngRow, lngCol)
                wsKD.Cells(lngRow - 2, intNum + 2) = wsManual.Cells(lngRow, lngCol)
            Next
    Next
    The problem is that even for a small number of rows (less that 10) it is very slow. How can I improve the speed?

  2. #2
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,968

    Re: Speed up the copying of data from one worksheet to another

    With just 10 rows and 20 columns per row, and your nested loop, you are still referencing the .cells property of your two sheets over 600 times by my calculations, which I would imagine has a bunch of overhead.

    If it were me, I would rewrite it all using for-each loops and ranges. I'm not able to provide any code, but below is a link that has some demo code for looping over cells in a range. Good luck.

    https://docs.microsoft.com/en-us/off...orksheet.range

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,175

    Re: Speed up the copying of data from one worksheet to another

    Have you thought about using an ADODB-Recordset with your sheet as its source?
    The Query could be like
    Code:
    rs.Open "SELECT * FROM [Sheet1$C3:Z20]", connection
    Then you could use "CopyFromRecordset"-Method....

    EDIT: If it's a contigous Range (as in: No skipping Cols/Rows/Cells)
    Code:
    Private cn As ADODB.Connection
    Private rs As ADODB.Recordset
    
    Sub main()
    Dim FileName As String
    Dim SQL As String
        FileName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
        Set cn = New ADODB.Connection
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";Extended Properties=""Excel 12.0 macro;HDR=NO;IMEX=0;"""
        cn.Open
        Set rs = New ADODB.Recordset
        SQL = "SELECT * FROM [Sheet1$C3:H20]"
        rs.Open SQL, cn
        Sheet2.Range("B2").CopyFromRecordset rs
    End Sub
    EDIT2: Even easier
    Code:
    Sub main()
        Sheet1.Range("D5:G10").Copy
        Sheet2.Range("C1").PasteSpecial
    End Sub
    Note: this places a "Selection"
    Last edited by Zvoni; Jun 23rd, 2022 at 02:21 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,587

    Re: Speed up the copying of data from one worksheet to another

    copy the complete ranges into arrays change all the data in the array and rewrite to cells, will be much faster
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,175

    Re: Speed up the copying of data from one worksheet to another

    OK, just looked at it again.
    He examines the value in wsManual.Cells and takes this as the Column(Offset) to place the value in wsKD

    Hmm.....me thinks you could solve this even without VBA, but with Excel-functions

    I'd need a sample Workbook for that though
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width