Try this:

==========
Sub test()
Dim ws As Worksheet
Dim Lastrow As Long, i As Long
For Each ws In ActiveWorkbook.Worksheets
With ws
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lastrow
.Cells(2, i + 1) = Mid(.Cells(i, 1), InStr(.Cells(i, 1), ":") + 1)
Next i
End With
Next ws
End Sub
=========

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Sheela" wrote:

I have a macro to copy from first column and then paste transpose into the
second row .

How do I just copy and paste only from a specific character €œ:€� in each cell?
Eg if in a cell the value is €œ abcd:ID€� , I would like to copy and paste
transpose only €œID€�.
I have the following code to just copy and paste everything in the cells. I
am not sure how to paste from only specific character.

Thank you very much in advance for you help.
I am using the following code:

Public Sub test()
Dim ws As Worksheet
Dim Lastrow As Long
For Each ws In ActiveWorkbook.Worksheets
With ws
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Range(.Cells(1, 1), .Cells(Lastrow, 1)).Copy
.Range(.Cells(2, 2), .Cells(2, Lastrow + 1)).PasteSpecial Paste:=xlPasteAll,
Transpose:=True
End With
Next ws
End Sub