write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Sunday 25 September 2011

Combine Multiple worksheets in single worksheet with in workbook

Sub Mtest()
Dim i As Integer
Dim ws As Worksheet
Worksheets(Worksheets.Count).Activate
For i = 1 To Worksheets.Count
'Change sheet name where you want to copy data from all other sheets
If Sheets(i).Name <> "Mainsheet" Then
'change you range here
Sheets(i).Range("A1:A25").Copy Destination:=Sheets("Mainsheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
'OR if header of all sheets are common  & copy used rows
'Sheets(i).UsedRange.Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Copy Destination:=Sheets("Mainsheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

No comments:

Post a Comment