VBA to copy, paste, update value and loop

[mena139]

Hello I am fairly new to VBA and have been playing around with a few different things for the last few days while tryng to accomplish the following:

I would like to:
1) copy from cell b3:b6
2) paste it in sheet2 in cell a5
3) (back on sheet1) copy cell D3
4) paste on sheet2 in cell A3
5) (back on sheet1) add 1 to the value in cell a1
6) repeat step 1
7) repeat step 2 but move over one column
8) repeat step 3
9) repeat step 4 but move over one column
10) repeat step 5
11) loop through this 200X

any tips / suggestions would be greatly appreciated

 

[SQUIDD]

hi

when you say repeat step 2 but move over one column, do youmean for the copy event or the paste event or both.

Dave

 

[mena139]

the paste event. I would like to continue to copy from the same range

 

[BQardi]

Here:

Sub LoopCopy()
Dim i As Long
Application.ScreenUpdating = False
With Sheet1
    For i = 1 To 200
        .Range("B3:B6").Copy
        Sheet2.Cells(5, i).PasteSpecial xlPasteValues
        .Range("D3").Copy
        Sheet2.Cells(3, i).PasteSpecial xlPasteValues
        .Range("A1").Value = .Range("A1").Value + 1
    Next i
End With
End Sub

 

[SQUIDD]

test this on a copy of your workbook

Sub copystuff()
d = 0
Do Until d = 20

    Worksheets("Sheet1").Range("B3:B6").Copy
    Worksheets("sheet2").Range("a5").Offset(, d).PasteSpecial
    
    Worksheets("Sheet1").Range("D3").Copy
    Worksheets("sheet2").Range("a3").Offset(, d).PasteSpecial
    
    Worksheets("sheet1").Range("a1") = Worksheets("sheet1").Range("a1") + 1

d = d + 1

Loop

End Sub

dave

 

[SQUIDD]

sorry

Do Until d = 20

should be

Do Until d = 200

dave