How to combine several sheets to one sheet
Category: [Excel] Demo Available
How to combine several sheets to one sheet
Requirement:
The user has multiple sheets in one workbook and I need combined data in one separate sheet. The user's data is from AA to AZ and all are dynamic rows.
Solution:
Code:
Sub CopyRange()
Application.ScreenUpdating = False
Dim LastRow As Long
Dim ws As Worksheet
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Combined"
Sheets(2).Rows(1).EntireRow.Copy Sheets("Combined").Cells(1, 1)
For Each ws In Sheets
If ws.Name <> "Combined" Then
LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws.Range("A2:Y" & LastRow).Copy Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next ws
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy non-blank rows in a range and paste to other sheets |
| How to crate a macro for text copy and paste in different worksheets based on a variable in Excel |
| How to use VBA code to compare two different sheets in a workbook |
| How to compare two sheets and paste the result in sheet 3 |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.