I have a workbook with multiple pages. Each page has data in only first column.
I like to copy the data from the first column and paste transpose into first
2 rows.
and then delete the first column.
I have the following code, but it is not working properly.
1) it is not looping through the excel pages.
2. It is deleting the entire data ( not just the first column) except the
first cell.


Can you tell me where the code is wrong and also can you suggest a more
efficient way to do this.
thanks in advance.
###
Public Sub Paste_Transpose()
Dim ws As Worksheet

Dim Lastrow As Long
For Each ws In ActiveWorkbook.Worksheets
Lastrow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row


Range(Cells(1, 1), Cells(Lastrow, 1)).Copy
Range(Cells(1, 1), Cells(2, Lastrow + 1)).PasteSpecial Paste:=xlPasteAll,
Transpose:=True
Range(Cells(1, 1), Cells(Lastrow, 1)).Delete
Next ws



End Sub

Sheela
Your code works on only the active sheet. The For loop loops through
all the sheets in the workbook, but your code says to work on only the
active sheet. So it does. I modified your code so that all the operations
are done to the ws sheet. HTH Otto
Public Sub Paste_Transpose()
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(1, 1), .Cells(2, Lastrow + 1)).PasteSpecial
Paste:=xlPasteAll, Transpose:=True
.Range(.Cells(1, 1), .Cells(Lastrow, 1)).ClearContents
End With
Next ws
End Sub

"Sheela" wrote in message
...
I have a workbook with multiple pages. Each page has data in only first
column.
I like to copy the data from the first column and paste transpose into
first
2 rows.
and then delete the first column.
I have the following code, but it is not working properly.
1) it is not looping through the excel pages.
2. It is deleting the entire data ( not just the first column) except the
first cell.


Can you tell me where the code is wrong and also can you suggest a more
efficient way to do this.
thanks in advance.
###
Public Sub Paste_Transpose()
Dim ws As Worksheet

Dim Lastrow As Long
For Each ws In ActiveWorkbook.Worksheets
Lastrow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row


Range(Cells(1, 1), Cells(Lastrow, 1)).Copy
Range(Cells(1, 1), Cells(2, Lastrow + 1)).PasteSpecial Paste:=xlPasteAll,
Transpose:=True
Range(Cells(1, 1), Cells(Lastrow, 1)).Delete
Next ws



End Sub

Thank you very much for the clarification.
I appreciate it.

sheela
"Otto Moehrbach" wrote:

Sheela
Your code works on only the active sheet. The For loop loops through
all the sheets in the workbook, but your code says to work on only the
active sheet. So it does. I modified your code so that all the operations
are done to the ws sheet. HTH Otto
Public Sub Paste_Transpose()
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(1, 1), .Cells(2, Lastrow + 1)).PasteSpecial
Paste:=xlPasteAll, Transpose:=True
.Range(.Cells(1, 1), .Cells(Lastrow, 1)).ClearContents
End With
Next ws
End Sub

"Sheela" wrote in message
...
I have a workbook with multiple pages. Each page has data in only first
column.
I like to copy the data from the first column and paste transpose into
first
2 rows.
and then delete the first column.
I have the following code, but it is not working properly.
1) it is not looping through the excel pages.
2. It is deleting the entire data ( not just the first column) except the
first cell.


Can you tell me where the code is wrong and also can you suggest a more
efficient way to do this.
thanks in advance.
###
Public Sub Paste_Transpose()
Dim ws As Worksheet

Dim Lastrow As Long
For Each ws In ActiveWorkbook.Worksheets
Lastrow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row


Range(Cells(1, 1), Cells(Lastrow, 1)).Copy
Range(Cells(1, 1), Cells(2, Lastrow + 1)).PasteSpecial Paste:=xlPasteAll,
Transpose:=True
Range(Cells(1, 1), Cells(Lastrow, 1)).Delete
Next ws



End Sub

Glad to help. Thanks for the feedback. Otto
"Sheela" wrote in message
...

Thank you very much for the clarification.
I appreciate it.

sheela
"Otto Moehrbach" wrote:

Sheela
Your code works on only the active sheet. The For loop loops through
all the sheets in the workbook, but your code says to work on only the
active sheet. So it does. I modified your code so that all the
operations
are done to the ws sheet. HTH Otto
Public Sub Paste_Transpose()
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(1, 1), .Cells(2, Lastrow + 1)).PasteSpecial
Paste:=xlPasteAll, Transpose:=True
.Range(.Cells(1, 1), .Cells(Lastrow, 1)).ClearContents
End With
Next ws
End Sub

"Sheela" wrote in message
...
I have a workbook with multiple pages. Each page has data in only first
column.
I like to copy the data from the first column and paste transpose into
first
2 rows.
and then delete the first column.
I have the following code, but it is not working properly.
1) it is not looping through the excel pages.
2. It is deleting the entire data ( not just the first column) except
the
first cell.


Can you tell me where the code is wrong and also can you suggest a more
efficient way to do this.
thanks in advance.
###
Public Sub Paste_Transpose()
Dim ws As Worksheet

Dim Lastrow As Long
For Each ws In ActiveWorkbook.Worksheets
Lastrow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row


Range(Cells(1, 1), Cells(Lastrow, 1)).Copy
Range(Cells(1, 1), Cells(2, Lastrow + 1)).PasteSpecial
Paste:=xlPasteAll,
Transpose:=True
Range(Cells(1, 1), Cells(Lastrow, 1)).Delete
Next ws



End Sub