VBA to iterate through rows, working with multiple worksheets

Question

I'd like some advice on creating a macro in VBA which will loop through an array of values in one worksheet, copying the values of two fields, pasting those values into another worksheet, then copying and pasting the output of a calculation made in those values back into the orignal worksheet next to the original rows.

In the (very trimmed down) example, I have 2 worksheets in a single workbook

 Worksheet:     Values
 Contains 4 columns. (A,B,C &D).  
 Columns A and B each contain a list of numbers
 Columns C and D are empty, waiting to be populated based on a 
 calculation made from columns A and B (calculation takes place in a seperate worksheet).

 Worksheet:     Formula
 Contains 2 fields to enter data (pasted from VALUES:colums A & B)
 Also contains 2 calculation fields which produce the output.  

This output then needs to be pasted back into "VALUES" at the respective rows in the blank columns.

Values Workseet Below:

Formula Workseet Below:

The process I've explained above in VBA goes something like this:

Sub value_paster()
'
' value_paster Macro
'

'
    Sheets("Values").Select
    Range("A2:B2").Select
    Selection.Copy
    Sheets("Formula").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("C2:D2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Values").Select
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

My next job is to wrap the above code into something useful which will repeat the same processes made in the "Forumla" worksheet while working its way down rows in the "Values" worksheet.

I've found numerous examples on how to loop/iterate through rows but nothing much on how to do it while jumping between worksheets and copying/pasting from one to the other etc.

Note: The real calculations made on the data in the worksheet are complex and can't be incorporated into the code.

Any advice gratefully appreciated.

EDIT: To clarify, I don't need any additional rows created in the Formulas worksheet - this worksheet is solely used to perform calculations on the data pasted from Values. The ouput generated in Formulas then needs to be pasted back into the Values worksheet in the 2 output columns - this is where the looping through rows needs to occur.

EDIT 2: I've created a gif demonstrating the manual process I'd like to replicate using VBA

Note this isn't the actual workbook I'll be using, it's just a quick demo for the purposes of this question)

Answer 1

I believe the code below will suit your needs, assuming all you need is the values of the cells and assuming exclusively the pattern you provided.

Please note that I'm also iterating the rows in Formula worksheet. If the calculation formulas are only in Cells C1 and D1 from Formula worksheet, the code will have to be changed.

Sub value_paster()

    Dim wsValues: Set wsValues = ThisWorkbook.Worksheets("Values")
    Dim wsFormula: Set wsFormula = ThisWorkbook.Worksheets("Formula")

    Dim iRow: iRow = 1
    Do While wsValues.Cells(iRow, 1).Value <> ""
        Dim lngA: lngA = wsValues.Cells(iRow, 1).Value
        Dim lngB: lngB = wsValues.Cells(iRow, 2).Value

        wsFormula.Cells(iRow, 1).Value = lngA
        wsFormula.Cells(iRow, 2).Value = lngB

        Dim lngC: lngC = wsFormula.Cells(iRow, 3).Value
        Dim lngD: lngD = wsFormula.Cells(iRow, 4).Value

        wsValues.Cells(iRow, 3).Value = lngC
        wsValues.Cells(iRow, 4).Value = lngD

        iRow = iRow + 1
    Loop

End Sub

EDIT: Based on OP's latest information, I'm adding an alternate solution. There is probably a better way to do it (I confess I'm not happy with the performance of this one) but I'm not able to improve this at the moment. Hopefully this will work for you at the moment:

Sub value_paster()

    Application.ScreenUpdating = False   ' To freeze screen while the sub is performed


    Dim wsValues: Set wsValues = ThisWorkbook.Worksheets("Values")
    Dim wsFormula: Set wsFormula = ThisWorkbook.Worksheets("Formula")

    Dim iRow: iRow = 2
    Do While wsValues.Cells(iRow, 1).Value <> ""
        Dim lngA: lngA = wsValues.Cells(iRow, 1).Value
        Dim lngB: lngB = wsValues.Cells(iRow, 2).Value

        wsFormula.Cells(2, 1).Value = lngA  ' You are making simple copy/paste here, so working with Selection can be avoided.
        wsFormula.Cells(2, 2).Value = lngB  ' Same goes here. Simple copy/paste can be done by assigning values, without using Selection

        wsFormula.Activate
        wsFormula.Range("C2:D2").Select
        Application.CutCopyMode = False
        Selection.Copy

        wsValues.Activate
        wsValues.Range(wsValues.Cells(iRow, 3), wsValues.Cells(iRow, 4)).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        iRow = iRow + 1
    Loop

    Application.ScreenUpdating = True   ' Reenables screen updating 


End Sub