F
fi.or.jp.de
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
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