paste transpose array of variables

[Sheela]

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

 

[JBeaucaire]

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
=========

 

[Sheela]

Thank you, it worked.

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
=========