VBA COPY AND PASTE IN A LOOP

Question

I am new to VBA and need some help with this problem.

I have 8 columns and each column has 9 rows. I want to copy each column and paste it to another sheet at a specific range.

How can i code the following statement? "Go to sheet2 and copy the first column and 2-9 rows and paste it to sheet1 in the range("C3:C10") and then press solver do some other staff and when all these are done go to the next column and do the same again".

I found how can I do this for one column but I can't find how to do the loop. Here is the code I've wrote for the first column. I want to do the same for all 8 columns.

Sub test1()

Worksheets("sheet 1").Range("c3:c10").Value = Worksheets("Sheet 2").Range("A2:A9").Value
Worksheets("Sheet 1").Range("C17").Value = "Y"
Worksheets("Sheet 1").Range("C32").Value = "Y"
        SolverOk SetCell:="$E$96", MaxMinVal:=3, ValueOf:=Range("C104").Value, 
            ByChange:="$C$100", _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True

Worksheets("Sheet 3").Range("J2").Value = Worksheets("Sheet 1").Range("L24").Value

End Sub

Answer 1

You could do it thus. If other things are variable can similarly adjust using i perhaps.

Sub test1()

Dim i As Long

For i = 1 To 8
    Worksheets("sheet 1").Range("c3:c10").Value = Worksheets("Sheet 2").Range("A2:A9").Offset(, i - 1).Value
    Worksheets("Sheet 1").Range("C17").Value = "Y"
    Worksheets("Sheet 1").Range("C32").Value = "Y"
    SolverOk SetCell:="$E$96", MaxMinVal:=3, ValueOf:=Range("C104").Value, _
             ByChange:="$C$100", Engine:=1, EngineDesc:="GRG Nonlinear"
    solverSolve True
    Worksheets("Sheet 3").Range("J2").Value = Worksheets("Sheet 1").Range("L24").Value
Next i

End Sub

Answer 2

Instead of using Range(""), I would loop between columns using .Cells(row, col), for example:

Sub Iteration()

    For c = 1 To 9
        Sheets("Sheet1").Cells(1, c).EntireColumn.Copy
        Sheets("Sheet2").Cells(1, c).PasteSpecial
    Next

End Sub

This code is copying every row between columns 1 and 9 from Sheet1 to Sheet2, by iterating through "Cells" second parameter, that references a column.

And the code can do this because of the "For" loop, which keeps repeating until the condition is reached. On my example, I set "c" to 1 and asked to repeat the codeinside the "For - Next" statement until c reaches 9, adding 1 to the variable by default.