copy/paste loop

Question

I am only a novice programer, so I am looking for some assitance getting started.

I have 3 sheets of data.  Sheet 3 has a list of ssns (A11:A??).  I'd like to copy each ssn to cell B8 on Sheet 1, then copy the formula results from Sheet 2 (E301:G302) to column FE on the same row where I grabbed the ssn from on sheet 3.  I'd like to loop through all ssns on sheet 3.

can anybody please provide with some sample code to get started?  thanks!

Answer 1

You could insert a loop between setting the value of the cell and copying/pasting the range:

    Dim i As Integer

    For i = 1 to 10

      DoEvents

    Loop

Answer 2

Change

    Worksheets("Sheet2").Range("E301:G301").Copy _

      Destination:=wsh.Range("FE" & r)

to

    Worksheets("Sheet2").Range("E301:G301").Copy

    wsh.Range("FE" & r).PasteSpecial Paste:=xlValues

Answer 3

You can't directly copy cells from two rows (301 and 302) into one...

Sub Loopy()

  Dim wsh As Worksheet

  Dim r As Long

  Dim m As Long

  Set wsh = Worksheets("Sheet3")

  m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row

  For r = 11 To m

    Worksheets("Sheet1").Range("B8") = wsh.Range("A" & r)

    Worksheets("Sheet2").Range("E301:G301").Copy _

      Destination:=wsh.Range("FE" & r)

  Next r

End Sub

Answer 4

The DoEvents peice seems to have solved the issue.  Thanks Hans!

Answer 5

I think the problem may have to do with the large about of calculations.  The output cells have roughly 300 preceding rows of look ups and calculations. 

Calculations are set to automatic .   Also, the forced calculation via the macro does not work.

I've also tried dropping in my final "output cells" formulas via the macro.  ...still does not work. 

Any other suggestions?