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

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

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