VBA copy-paste efficiency

[Gojira]

Hi,

I want to copy the values from a non-contiguous vertically arranged range of data (a mixture of strings and currency) from a source workbook into a contiguous horizontally arranged dataset in a different workbook. I've gotten the following code so far, but is there a way to do it faster?

        wbDest.Sheets("Data").Range("A" & i).Value = wbSource.Sheets("Summary").Range("B1").Value
        wbDest.Sheets("Data").Range("B" & i).Value = wbSource.Sheets("Summary").Range("B2").Value
        wbDest.Sheets("Data").Range("C" & i).Value = wbSource.Sheets("Summary").Range("B3").Value
        wbDest.Sheets("Data").Range("D" & i).Value = wbSource.Sheets("Summary").Range("B4").Value
        wbDest.Sheets("Data").Range("E" & i).Value = wbSource.Sheets("Summary").Range("B6").Value
        wbDest.Sheets("Data").Range("F" & i).Value = wbSource.Sheets("Summary").Range("B8").Value
        wbDest.Sheets("Data").Range("G" & i).Value = wbSource.Sheets("Summary").Range("B15").Value
        wbDest.Sheets("Data").Range("H" & i).Value = wbSource.Sheets("Summary").Range("B16").Value
        wbDest.Sheets("Data").Range("I" & i).Value = wbSource.Sheets("Summary").Range("B17").Value

Some of the source cells have formulae, so I only want the values.

When I run the macro it takes several seconds to process. I need to scale this up so it's looping through up to 100 separate source files and am trying to be as efficient as I can. Is there a different approach I should consider?

 

[Joe4]

The way you are doing it is much more efficient than using Copy/Paste.
Try temporarily shutting of Screen Updating and Calculations, and see if that speeds things up:

        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        wbDest.Sheets("Data").Range("A" & i).Value = wbSource.Sheets("Summary").Range("B1").Value
        wbDest.Sheets("Data").Range("B" & i).Value = wbSource.Sheets("Summary").Range("B2").Value
        wbDest.Sheets("Data").Range("C" & i).Value = wbSource.Sheets("Summary").Range("B3").Value
        wbDest.Sheets("Data").Range("D" & i).Value = wbSource.Sheets("Summary").Range("B4").Value
        wbDest.Sheets("Data").Range("E" & i).Value = wbSource.Sheets("Summary").Range("B6").Value
        wbDest.Sheets("Data").Range("F" & i).Value = wbSource.Sheets("Summary").Range("B8").Value
        wbDest.Sheets("Data").Range("G" & i).Value = wbSource.Sheets("Summary").Range("B15").Value
        wbDest.Sheets("Data").Range("H" & i).Value = wbSource.Sheets("Summary").Range("B16").Value
        wbDest.Sheets("Data").Range("I" & i).Value = wbSource.Sheets("Summary").Range("B17").Value
        
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True

 

[offthelip]

There is a way to do this which will be 500 to 1000 times faster and that is to use variant arrays
I have to say this so often on this forum that I have this paragraph ready to copy and paste:
One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.
So instead of writing a loop which loops down a range copying one row at a time which will take along time if you have got 50000 rows it is much quicker to load the 50000 lines into a variant array ( one worksheet access), then copy the lines to a variant array and then write the array back to the worksheet, ( one worksheet access for each search that you are doing),
I have a simple rule for fast VBA: NEVER ACCESS THE WORKSHEET IN A LOOP.

So to rewrite your code as it stands try this ( untested)

Dim outarr(1 To 1, 1 To 9) As Variant


wbSource.Activate
With Sheets("Summary")
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
' load variant array with  variables
inarr = Range(.Cells(1, 2), .Cells(lastrow, 2))
End With
' copy to output array
outarr(1, 1) = inarr(1, 1)
outarr(1, 2) = inarr(1, 2)
outarr(1, 3) = inarr(1, 3)
outarr(1, 4) = inarr(1, 4)
outarr(1, 5) = inarr(1, 6)
outarr(1, 6) = inarr(1, 8)
outarr(1, 7) = inarr(1, 15)
outarr(1, 8) = inarr(1, 16)




wbSource.Activate
With Sheets("Date")
i = 1
'write out the output array
 Range(.Cells(i, 1), .Cells(i, 9)) = outarr
End With

I suspect that you have only given us part of the code ( because of "i" in the cells references) if this is the case you just need to define the outputarray to maximum size and reference it by i as you write in the loop and then only write it out to the worksheet once at the end.