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)

The data from Values should always be pasted into A2:B2 range from Formula, or should they also be pasted moving down the rows along with Values. You need to copy/paste just the values? — Victor Moraes
Hi Victor, the data needs to be pasted into "Values" moving down the rows, so each copy and paste procedure takes place in the next row number. — Huskie69
I know you wont like this but copying the data is not the answer. While you say the the real calculations made on the data in the worksheet are complex and can't be incorporated into the code actually that is by far the best way to do it. The logic is so much easier to see in VB. Just break it down into bit sized bits first, much like you might engineer a complex formula in Excel. The simplest way to loop through rows is with a for loop, this is a good explanation - homeandlearn.org/excel_vba_for_loops.html — Absinthe
Hi Absinthe. I appreciate that it would in theory be a lot easier to apply the transformation formula to each row, but the actual forumla is really very complex (I didn't create it, it's GIS datum transformation which is pretty hardcore stuff). As such, as a quick fix (so I don't have to learn PhD level maths!) like to solve it using "easier" VBA! — Huskie69

Victor Moraes Victor Moraes · Accepted Answer · 2016-06-21T12:02:23

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

VBA to iterate through rows, working with multiple worksheets

2 Answers