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

Public Sub test()
Dim ws As Worksheet
Dim Lastrow As Long
For Each ws In ActiveWorkbook.Worksheets
With ws
ColCount = 2
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 to LastRow
if instr(.Range("A" & RowCount),"€œ:€�") 0 then
..Range("A" & RowCount).copy _
destination:=.cells(2,ColCount)
ColCount = ColCount + 1
End if
Next RowCount
End With
Next ws
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145154

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

Thank you, it worked.

"JBeaucaire" wrote:

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