PDA

View Full Version : Find a particular text in column A and copy the value from next column and transpose



rkulasekaran
02-25-2016, 07:46 AM
Hello All,

I need a macro to find a particular text in column A ("Label:") and copy the value from next column B which is highlighted in red color into a new sheet and also transpose the values from columns A after "Modif Desc" to into the new sheet as Rows. I have attached the sample excel file. Kindly do have a look over it.

Thanks in advance.

skywriter
02-25-2016, 01:29 PM
Try this.


Sub rkulasekaran()
Dim rngData As Range, ws1 As Worksheet, ws2 As Worksheet, nr As Long, j
nr = 1
Set ws1 = Worksheets("Sheet 1")
Worksheets.Add.Name = "e.output2"
Set ws2 = ActiveSheet
With ws1
Set rngData = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With
For Each j In rngData.SpecialCells(2, 1).Areas
ws2.Cells(nr, 1).Value = j(-2, 2).Value
ws2.Cells(nr, 2).Resize(, j.Rows.Cells.Count) = Application.Transpose(j)
nr = nr + 1
Next j
End Sub

rkulasekaran
02-28-2016, 08:17 PM
Hi,
Thanks for the reply, i tried the same with other worksheet. It didn't extract properly. Do help me out. Thanks in advance.