Change range of cells within VBA macro and copy and paste to fixed cell range

why are you using a loop here?

you can accomplish the same with this

Range("C41:F57").Copy Destination: Range ("C26")

I do not want to copy the range C41:F57 as a chunk. I want to copy as chunks of 4 cells a row at a time. That is copy C40:F40 and paste it to C26:F26. Then run some calculation on C26:F26 (unrelated to this discussion). Then I want to repeat this sequence on C41:F41 copy and paste to C26:F26. Then do the same for C42:F42 upto C57:F57.

So I basically need a loop to copy and paste 4 cells each and go down to the next row and repeat it 17 times.

See if this works for you

Sub CopyLoop()

Dim i As Long

For i = 41 To 57
    Cells(i, 3).Resize(, 4).Copy Destination:=Cells(26, 3)
    
    'Any other calculation will have o be placed here
    
Next i

End Sub

I am not following you man.

Can you give me a sample of your workbook. Also you can go through the code by pressing "F8" it will go lineby line, so you can see what the code is doing.

You are resizing when there is no need to. You are copying only one cell. Ex

If you want to copy range "A1:B2" then you pic the start and resize it. Range("A1").resize(0,2) which is a little ambiguous.

So here is the code take a look and tell me if it works. I modified it to match the Values only.

Sub CopyLoop()

Dim i As Long

For i = 40 To 57
    Cells(26, 3).Resize(, 4).Value = Cells(i, 3).Resize(, 4).Value
    Cells(i, 7).Value = Cells(34, 2).Value
    Cells(i, 8).Value = Cells(35, 2).Value
           
Next i

End Sub

Originally Posted by Mannyny

fredlo2010

Code:

Cells(i, 7).Value = Cells(34, 2).Value
Cells(i, 8).Value = Cells(35, 2).Value

Didnt work as expected. It not only copies B34 and B35 to G40 and H40 but goes through G40:H57 and copies to the whole range.
Not sure how the loop from 40 to 57 interferes here. Every time it goes through the loop, B34:35 are overwritten through out the range
G40:H57 and NOT just its respective row cells.

Thanks

I am totally lost now. The code works according to what your explanation says.Maybe we are not understanding each other (it happens sometimes) Can you provide a copy of your workbook with a before and after sheet?

Thanks

@ Mannyny

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules (Rule #3) require them so please keep that in mind and add them yourself whenever showing code in any of your future posts.
Thanks.