3
\$\begingroup\$

This code:

  1. Copies a value from "Sheet1"
  2. Pastes special (as a number) in another sheet "Sheet2"
  3. Jumps to the next line

I already:

  1. Disabled screen refresh

  2. Wrapped the code with Application.EnableEvents = False and Application.Calculation = xlCalculationManual

    1 made the calculation a little faster, but 2 didn't seem to make any effect.

Dim iterations, counter As Integer
iterations= 10000
counter = 1

Do While counter <= iterations

' copy value from Sheet1
Sheets("Sheet1").Range("B4").Copy

' paste value in sheet2
Sheets("Sheet2").Select

Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).Range("A1").Select

counter = counter + 1

Loop

I usually run 50,000+ iterations (counter = 50000).

How can I improve this code speed?

\$\endgroup\$
2
  • 1
    \$\begingroup\$ hi @m3dl and welcome to Code Review. We can review your code as-is but we could probably help even more if you provided the entire Sub that it runs in. \$\endgroup\$ Commented Aug 1, 2016 at 8:09
  • 1
    \$\begingroup\$ What exactly is your objective? If the copy is always going to be the same, you can simply move the Sheets("Sheet1").Range("B4").Copy before the while loop. \$\endgroup\$ Commented Aug 1, 2016 at 10:30

1 Answer 1

Reset to default
3
\$\begingroup\$

if your aim is to:

  • copy the value in cell "B4" of worksheet "Sheet1"

  • and paste it in worksheets "Sheet2" from cell "A1" downwards in iterations cells

then simply use:

Dim iterations: iterations = 1000    
Worksheets("Sheet2").range("A1").Resize(iterations).value = Worksheets("Sheet01").range("B4").value

if your aim is to:

  • copy the value from cell "B4" of worksheet "Sheet1" and down to iterations cells

  • and paste them in worksheets "Sheet2" from cell "A1" downwards in iterations cells

then simply use:

Dim iterations: iterations = 1000    
Worksheets("Sheet2").range("A1").Resize(iterations).value = Worksheets("Sheet01").range("B4").Resize(iterations).value
\$\endgroup\$
4
  • \$\begingroup\$ The first option is my objective. Your code worked, but it pastes the same value in the 1,000 lines. I would like to recalculate the spreadsheet before get the value of B4, since its value changes with a =RAND() function. So, I need to recalculate the spreadsheet using Worksheets(“Sheet1”).Calculate, but I don't know how to use this .calculate in your answer. \$\endgroup\$ Commented Aug 1, 2016 at 17:06
  • \$\begingroup\$ is your aim simply to have iterations random numbers in "Sheet2" from cell "A1" downwards? \$\endgroup\$ Commented Aug 2, 2016 at 6:16
  • \$\begingroup\$ Sorry, I didn't explained myself in a clear way. Not exactly. B4 is a VLOOKUP that depends on a RAND() fuction, so B4 changes value when the spreadsheet is recalculated. \$\endgroup\$ Commented Aug 2, 2016 at 16:39
  • 1
    \$\begingroup\$ then you speed issue is totally derived from sheet calulations speed. The only way to improve it is take all that calculations off the sheet and into VBA code \$\endgroup\$ Commented Aug 2, 2016 at 17:36

You must log in to answer this question.