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

Thanks Hans for all your help.  Hopefully just one last question.

The cells in sheet 2 E301:G301 aren't being recalculated.  My calculation is turned on to automatic, but it seems that the copy paste event is occuring before the cells have had a chance to fully recalculate.

I've tried to force the recalculation using the following code, but it still does not seem to work.

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

Worksheets("Sheet1").calculate

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

I know my formulas are working, because when I manually click in the cells, they recalculate.  Any suggestions?

Answer 5

One quick question- how would I manipulate to paste as values?