paste transpose array of variables
 

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

paste transpose array of variables
 

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

paste transpose array of variables
 

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

paste transpose array of variables
 

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