Copy paste loop VBA

[kamiljaku]

Hi guys,

Im just starting with VBA.

could you help me and tell how I can do this operation with a loop. Now Im changing the offset values manually but I know there is a loop possible.

Could you please tell me how to do it with explanation.




Sub iex()


Dim i As Integer
Dim y As Integer


y = 1


i = 107


Range(Cells(17, 5), Cells(113, 5)).Copy Destination:=Cells(17, 45)
Range(Cells(17 + i, 5), Cells(113 + i, 5)).Copy Destination:=Cells(17, 45 + y)
Range(Cells(17 + 2 * i, 5), Cells(113 + 2 * i, 5)).Copy Destination:=Cells(17, 45 + 2 * y)
Range(Cells(17 + 3 * i, 5), Cells(113 + 3 * i, 5)).Copy Destination:=Cells(17, 45 + 3 * y)
Range(Cells(17 + 4 * i, 5), Cells(113 + 4 * i, 5)).Copy Destination:=Cells(17, 45 + 4 * y)
Range(Cells(17 + 5 * i, 5), Cells(113 + 3 * i, 5)).Copy Destination:=Cells(17, 45 + 3 * y)
.
.
.




End Sub

 

[My Aswer Is This]

Please explain in words what your wanting to do.
I see your code but do not understand what your trying to do.

 

[kamiljaku]

Ive got some data in row D called ACT - these are volumes which we received at a certain time interval.

And this data is going down, when 1 day ends the next starts and so on.

[TABLE="width: 136"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Time[/TD]
[TD]Revised[/TD]
[TD]Act[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:00 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12:15 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12:30 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12:45 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1:00 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1:15 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1:30 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1:45 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2:00 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2:15 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2:30 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2:45 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3:00 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3:15 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3:30 AM[/TD]
[TD]0.00[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


I need to gather the volumes horizontal, next to each other like this.

[TABLE="width: 199"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:00 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12:15 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12:30 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12:45 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1:00 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1:15 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1:30 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1:45 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2:00 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2:15 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2:30 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2:45 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3:00 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3:15 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3:30 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3:45 AM[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Is this what you want

Sub iex()
   Dim i As Long
   Dim y As Long
   
   For i = 0 To 434 Step 107
      Range(Cells(17, 5), Cells(113, 5)).Offset(i).Copy Cells(17, 45).Offset(, j)
      y = y + 1
   Next i
End Sub

 

[kamiljaku]

Is this what you want

Sub iex()
   Dim i As Long
   Dim y As Long
   
   For i = 0 To 434 Step 107
      Range(Cells(17, 5), Cells(113, 5)).Offset(i).Copy Cells(17, 45).Offset(, j)
      y = y + 1
   Next i
End Sub

Works perfectly

I have another issue if I may ask for your help.

I need to change 15 min intervals to 30 minutes intervals like follows

4 7 3
4 2 3
0 2 3
0 0 0
0 0 0
0 0 0


to

8 9 6
0 2 3
0 0 0

Ive got a macro which is doing the job the other way - splits 30 min into 15 minutes. Do you know how to adjust it to do it the other way?

Sub dzielenie()


Dim vInput As Variant
Dim rOutput As Range, vOutput As Variant
Dim i As Integer, j As Integer
Dim dQuarter As Double
Dim iInterval As Integer


iInterval = 2 'How many Intervals


vInput = Selection
Set rOutput = Application.InputBox("Select destination cell (can be the same as the input range)", Type:=8)


ReDim vOutput(LBound(vInput) To UBound(vInput) * iInterval + 1, LBound(vInput, 2) To UBound(vInput, 2))


For i = LBound(vInput) To UBound(vInput) * iInterval
For j = LBound(vInput, 2) To UBound(vInput, 2)
vOutput(i, j) = vInput(Int((i - 1) / iInterval) + 1, j) / iInterval
Next j
Next i


rOutput.Resize((UBound(vInput) - LBound(vInput) + 1) * iInterval, UBound(vInput, 2) - LBound(vInput, 2) + 1).Value = vOutput


End Sub

 

[Fluff]

As this is a totally different question you'll need to start a new thread