Excel - Merge several worksheets into a single one(thanks)

jaja599 Posts 2 Registration date Sunday January 25, 2015 Status Member Last seen January 25, 2015 - Jan 25, 2015 at 05:39 PM
 Linds - Oct 14, 2015 at 05:34 PM

I use Excel to store information about several users and their cars information and have a different worksheet for each month. How to merge all these worksheets into single page? All worksheets the same number of columns, with the same title (in the top row). whenever i modify my month sheets i want to affect the merge sheet
How to proceed?

Thank you for very your help guys


1 response

Write this for myself on Friday for a project of my own:

See if it works for you:

Option Explicit

Function WorksheetExists(ByVal WorksheetName As String) As Boolean
Dim Sht As Worksheet

For Each Sht In ThisWorkbook.Worksheets
If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then
WorksheetExists = True
Exit Function
End If
Next Sht
WorksheetExists = False
End Function

Sub MergeSheets()
Dim i As Integer
Dim NumberofSheets As Integer
Dim xWs As Worksheet
Dim ActiveSheetLastRow As Long
Dim MergedLastRow As Long

Dim sheetno As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Does a sheet called Merge already exist? If so, delete it.
If WorksheetExists("Merge") Then Worksheets("Merge").Delete

' Get a count of the number of sheets in the workbook
NumberofSheets = Application.Sheets.Count

' Create a new sheet to store merged sheets

' Make the new sheet (which is also the active sheet) the last sheet in the workbook.
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)

'Rename new Sheet
ActiveSheet.Name = "Merge"

'Copy each sheet to the new Office
For i = 1 To NumberofSheets
Set xWs = Sheets(i)

'Find how many rows to copy
ActiveSheetLastRow = xWs.Range("A" & Rows.Count).End(xlUp).Row

'Find the next available row in the Merge sheet
MergedLastRow = Worksheets("Merge").Range("A" & Rows.Count).End(xlUp).Row

' Only copy the header row when copying the 1st sheet.
sheetno = Abs(i > 1) + 1

xWs.Range("A" & sheetno & ":J" & ActiveSheetLastRow).Copy Destination:=Worksheets("Merge").Range("A" & MergedLastRow)
Next i

End Sub
Thank you very much for this macro! It is SO close to what I need!

I would like this same macro to list the name of the worksheet in a row before the data from that worksheet is listed on the new Merge sheet. Can anyone please tell me how to do this?

Thanks in advance for your time -