vaemps
04-13-2021, 11:09 PM
I have code like this:
Private Sub CommandButton2_Click()
Dim i As Long
Dim r As Long
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
With Sheets("sheet1")
Set Rng1 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set Rng2 = .Range("M2", .Range("M" & Rows.Count).End(xlUp))
Set Rng3 = .Range("L2", .Range("L" & Rows.Count).End(xlUp))
End With
For i = 3 To Rng1.Count * 6 Step 6
If Rng3.Value = 1 Then
Sheets("sheet2").Range("A1:B5").Copy _
Destination:=Sheets("sheet3").Range("A" & i)
Elseif Rng3.Value = 2 Then
Sheets("sheet2").Range("A5:B9").Copy _
Destination:=Sheets("sheet3").Range("A" & i)
End If
r = r + 1
Sheets("sheet3").Range("A" & i).Value = Rng1(r).Value
Sheets("sheet3").Range("B" & i).Value = Rng2(r).Value
Next i
End Sub
Everything else I want is working properly, as it loops columns and inserts correct data to columns A and B. For the range.copy function I cannot get to work as I want it to. I want it to loop through column L and select correct range for copy based on that value. How I get this to work?
Private Sub CommandButton2_Click()
Dim i As Long
Dim r As Long
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
With Sheets("sheet1")
Set Rng1 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set Rng2 = .Range("M2", .Range("M" & Rows.Count).End(xlUp))
Set Rng3 = .Range("L2", .Range("L" & Rows.Count).End(xlUp))
End With
For i = 3 To Rng1.Count * 6 Step 6
If Rng3.Value = 1 Then
Sheets("sheet2").Range("A1:B5").Copy _
Destination:=Sheets("sheet3").Range("A" & i)
Elseif Rng3.Value = 2 Then
Sheets("sheet2").Range("A5:B9").Copy _
Destination:=Sheets("sheet3").Range("A" & i)
End If
r = r + 1
Sheets("sheet3").Range("A" & i).Value = Rng1(r).Value
Sheets("sheet3").Range("B" & i).Value = Rng2(r).Value
Next i
End Sub
Everything else I want is working properly, as it loops columns and inserts correct data to columns A and B. For the range.copy function I cannot get to work as I want it to. I want it to loop through column L and select correct range for copy based on that value. How I get this to work?