S
Sheela
I have a macro to find a last column in all the worksheets in a workbook.
In some sheets it is correctly selecting the last column, but in some sheets
it is passing the last column and selecting some other column on way right to
it.
I tried to use the following two methods, both are working the same way.
Could someone figure out the correct way to find the last column?
Public Sub test()
Dim ws As Worksheet
Dim Lastrow, Lastcolumn As Long
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
With ws
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
..Cells(Lastrow, 1).Copy
..Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn +
1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True
End With
Next ws
On Error GoTo 0
End Sub
In some sheets it is correctly selecting the last column, but in some sheets
it is passing the last column and selecting some other column on way right to
it.
I tried to use the following two methods, both are working the same way.
Could someone figure out the correct way to find the last column?
Public Sub test()
Dim ws As Worksheet
Dim Lastrow, Lastcolumn As Long
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
With ws
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
..Cells(Lastrow, 1).Copy
..Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn +
1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True
End With
Next ws
On Error GoTo 0
End Sub