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

If the cells are recalculated when you click in them, I'd say calculation is not fully automatic...

Answer 5

I would suggest to have the calculation done in VBA itself; rather copying the formula results from the other sheet.