CODEWITHSUNDEEP
vbaexcel
JordanCA57
JordanCA57

Reputation: 125

Loop macro and change ranges with each loop

I’m starting with a worksheet “Med” that has formulas/data/formatting in cells A4:P9. I need to copy those cells to cell A10 (6 rows down). I then need to fill in some of the cells from information in another worksheet “Data”. I need to repeat this the same number of times as the count of Data!(A:A) -1 dropping down 6 rows each time I copy the information.

The data that I need to fill in comes from the Sheet "Data" and moves over one column for each copy in Sheet “Med”.

I have the code to make the first copy, but don’t have any idea where to go from here. Looking at the code below the next copy would go to A16 (6 rows down from A10).

The ranges from the worksheet “Med” would also increase by 6 rows and the columns would stay the same.

The ranges from the worksheet “Data” would move over on column and the row numbers would stay the same.

Jordan

Sub Macro1()
    Dim wsData As Worksheet 
    Dim wsMed As Worksheet 
    Set wsData = Sheets("Data") 
    Set wsMed = Sheets("Med")

    'Copy data set
    wsMed.Range("A4:P9").Copy wsMed.Range("A10") 'Set Premium Values
    wsMed.Range("M11").Value = wsData.Range("C20").Value
    wsMed.Range("M12").Value = wsData.Range("C21").Value
    wsMed.Range("M13").Value = wsData.Range("C22").Value
    wsMed.Range("M14").Value = wsData.Range("C23").Value 'Set Assumptions
    wsMed.Range("L11").Value = wsData.Range("C24").Value
    wsMed.Range("L12").Value = wsData.Range("C25").Value
    wsMed.Range("L13").Value = wsData.Range("C26").Value
    wsMed.Range("L14").Value = wsData.Range("C27").Value 
End Sub

Upvotes: 1

Views: 1642

Answers (1)

teylyn
teylyn

Reputation: 35990

This macro runs the code three times

Sub Macro1()
    Dim wsData As Worksheet
    Dim wsMed As Worksheet
    Set wsData = Sheets("Data")
    Set wsMed = Sheets("Med")
    Dim i As Integer, j As Integer, x As Integer

    i = 10
    j = 3


    'Copy data set
    For x = 1 To 3 ' run 3 times
        wsMed.Range("A4:P9").Copy wsMed.Cells(i, 1) 'Set Premium Values
        wsMed.Range("M" & i + 1).Value = wsData.Cells(20, j).Value
        wsMed.Range("M" & i + 2).Value = wsData.Cells(21, j).Value
        wsMed.Range("M" & i + 3).Value = wsData.Cells(22, j).Value
        wsMed.Range("M" & i + 4).Value = wsData.Cells(23, j).Value 'Set Assumptions
        wsMed.Range("L" & i + 1).Value = wsData.Cells(24, j).Value
        wsMed.Range("L" & i + 2).Value = wsData.Cells(25, j).Value
        wsMed.Range("L" & i + 3).Value = wsData.Cells(26, j).Value
        wsMed.Range("L" & i + 4).Value = wsData.Cells(27, j).Value
        i = i + 6
        j = j + 1
    Next x

End Sub

Upvotes: 3

Related Questions