VBA and macros to copy values and insert into column (left to right)

[Legacy 485190]

Hi all,

I am having difficulties to use vba and macros, to copy values (rows of data) and paste as value into a column after it. I tried using Record macros but failed to get the result as the column that I need to select and copy from always changes.

This is the origin report :

		Test Cycle 1
1	Test 1	Pass
2	Test 2	Pass
3	Test 3	Pass
4	Test 4	Pass
5	Test 5	Pass

		Test Cycle 2
1	Test 1	Fail
2	Test 2	Pass
3	Test 3	Pass
4	Test 4	Pass
5	Test 5	Pass

		Test Cycle 3
1	Test 1	Fail
2	Test 2	Pass
3	Test 3	Pass
4	Test 4	Pass
5	Test 5	Pass

I need the result like this :

		Test Cycle 1	Test Cycle 2	Test Cycle 3	Test Cycle 4
1	Test 1	Pass	Fail	Pass	Pass
2	Test 2	Pass	Pass	Fail	Pass
3	Test 3	Pass	Pass	Pass	Pass
4	Test 4	Pass	Pass	Pass	Pass
5	Test 5	Pass	Pass	Pass	Pass

Can anyone help me ? I need to automate this process.

Thanks in advance.

 

[Zot]

But my sheet name is also Sheet2
View attachment 50233

Strange? We are using same Excel version and the code was tested okay.

 

[Zot]

Okay. I should have avoid putting conflicting name since row is also in VBA term. My bad. Try

Sub Test()

Dim nRow As Long, nCol As Long
Dim rngRowFound As Range, rngResult As Range
Dim cell As Range, rngData As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Sheets(1)
Set ws2 = ActiveWorkbook.Sheets(2)

Set rngData = ws1.Range("B1", ws1.Cells(Rows.Count, "B").End(xlUp))

For Each cell In rngData
    Select Case cell
        Case ""
        If cell.Offset(0, 1) Like "Test Cycle*" Then
            nCol = CLng(Trim(Split(cell.Offset(0, 1), "Test Cycle")(1))) + 2
            ws2.Cells(1, nCol) = cell.Offset(0, 1)
        End If
    Case Else
        Set rngResult = ws2.Range("B2", ws2.Cells(Rows.Count, "B").End(xlUp))
        If rngResult.row = 1 Then Set rngResult = ws2.Range("B2")
        Set rngRowFound = rngResult.Find(cell.Value, LookAt:=xlWhole)
        If rngRowFound Is Nothing Then
            nRow = ws2.Cells(Rows.Count, "B").End(xlUp).Offset(1).row
            With ws2.Cells(nRow, nCol - 1)
                .Value = cell
                .Offset(0, 1) = cell.Offset(0, 1)
            End With
        Else
            With ws2.Cells(rngRowFound.row, nCol)
                .Value = cell.Offset(0, 1)
            End With
        End If
    End Select
Next

End Sub

 

[Legacy 485190]

Strange? We are using same Excel version and the code was tested okay.

 

[Legacy 485190]

Okay. I should have avoid putting conflicting name since row is also in VBA term. My bad. Try

Sub Test()

Dim nRow As Long, nCol As Long
Dim rngRowFound As Range, rngResult As Range
Dim cell As Range, rngData As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Sheets(1)
Set ws2 = ActiveWorkbook.Sheets(2)

Set rngData = ws1.Range("B1", ws1.Cells(Rows.Count, "B").End(xlUp))

For Each cell In rngData
    Select Case cell
        Case ""
        If cell.Offset(0, 1) Like "Test Cycle*" Then
            nCol = CLng(Trim(Split(cell.Offset(0, 1), "Test Cycle")(1))) + 2
            ws2.Cells(1, nCol) = cell.Offset(0, 1)
        End If
    Case Else
        Set rngResult = ws2.Range("B2", ws2.Cells(Rows.Count, "B").End(xlUp))
        If rngResult.row = 1 Then Set rngResult = ws2.Range("B2")
        Set rngRowFound = rngResult.Find(cell.Value, LookAt:=xlWhole)
        If rngRowFound Is Nothing Then
            nRow = ws2.Cells(Rows.Count, "B").End(xlUp).Offset(1).row
            With ws2.Cells(nRow, nCol - 1)
                .Value = cell
                .Offset(0, 1) = cell.Offset(0, 1)
            End With
        Else
            With ws2.Cells(rngRowFound.row, nCol)
                .Value = cell.Offset(0, 1)
            End With
        End If
    End Select
Next

End Sub

Still have the same error

 

[Zot]

When you stuck at that row, move your cursor to hover on nRow and nCol. See if any value popped up. However, it seem like ws2 has not been defined which cannot be right. Something weird here.

 

[Legacy 485190]

When you stuck at that row, move your cursor to hover on nRow and nCol. See if any value popped up. However, it seem like ws2 has not been defined which cannot be right. Something weird here.

The nRow is 2 and the nCol is 0

 

[Zot]

Your sheet 1 is like this?

Bookcol.xlsm
	A	B	C
1			Test Cycle 1
2	1	Test 1	Pass
3	2	Test 2	Pass
4	3	Test 3	Pass
5	4	Test 4	Pass
6	5	Test 5	Pass
7
8			Test Cycle 2
9	1	Test 1	Fail
10	2	Test 2	Pass
11	3	Test 3	Pass
12	4	Test 4	Pass
13	5	Test 5	Pass
14
15
16			Test Cycle 3
17	1	Test 1	Fail
18	2	Test 2	Pass
19	3	Test 3	Pass
20	4	Test 4	Pass
21	5	Test 5	Pass
Sheet1

 

[Legacy 485190]

Your sheet 1 is like this?

Bookcol.xlsm
	A	B	C
1			Test Cycle 1
2	1	Test 1	Pass
3	2	Test 2	Pass
4	3	Test 3	Pass
5	4	Test 4	Pass
6	5	Test 5	Pass
7
8			Test Cycle 2
9	1	Test 1	Fail
10	2	Test 2	Pass
11	3	Test 3	Pass
12	4	Test 4	Pass
13	5	Test 5	Pass
14
15
16			Test Cycle 3
17	1	Test 1	Fail
18	2	Test 2	Pass
19	3	Test 3	Pass
20	4	Test 4	Pass
21	5	Test 5	Pass
Sheet1

This is my sheet 1

 

[Zot]

That is why nCol cannot be found because I was looking for Test Cycle 1 and it is located elsewhere. That is why it is important to have exact copy unless you just want to get the idea and can modify code as required.

 

[Legacy 485190]

That is why nCol cannot be found because I was looking for Test Cycle 1 and it is located elsewhere. That is why it is important to have exact copy unless you just want to get the idea and can modify code as required.

Because this is my first time using VBA code and now I know where is the problem but still no have any idea to solve the errors. ?