Select used range
That's good one, thanks mike
but one thing in your it will copy used range but i want to ignore the
header row of all the sheets.
so how can i modify the code.
On Feb 19, 1:00*am, Mike H wrote:
Hi,
From your mmessage header I guess your trying to copy the used range of each
sheet so try this
Sub group()
Dim i As Long
For i = 2 To Sheets.Count
lastrow = Worksheets("total").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets(i).UsedRange.Copy _
Destination:=Worksheets("total").Cells(lastrow + 1, 1)
Next
End Sub
Mike
" wrote:
Hi,
I have workbook with 5 to 6 sheets.
I need to copy all the details to sheet named "TOTAL"
Below code will go through all the sheets & from Cell A2 to used range
it will copy and paste in sheet "TOTAL"
Below code some problem i guess,
I have used If condition, can anyone give more simple coding better
than if conditon.
and Is there any other coding (ActiveSheet.UsedRange.Rows.Count ) to
count the number rows used and column apart from what ever i have
mentioned ?
Range("A2", Range("A2").Offset(lastrow, lastcol)).Select - Better line
code than this ? can anyone suggest me.
Sub group()
Dim i As Integer
Application.ScreenUpdating = False
For i = 2 To Sheets.Count
Worksheets(i).Select
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count
Range("A2", Range("A2").Offset(lastrow, lastcol)).Select
Selection.Copy
Range("A1").Select
Worksheets("total").Select
Range("A2").Select
If Range("A2").Value = "" Then
* *ActiveSheet.Paste
* * Else
* *Selection.End(xlDown).Select
* ActiveCell.Offset(1, 0).Select
* * ActiveSheet.Paste
End If
Next
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
|