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

[Closed]
Report
Posts
2
Registration date
Sunday January 25, 2015
Status
Member
Last seen
January 25, 2015
-
 Linds -
Hello,

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 reply

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
Worksheets.Add

' 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 -

Lindsey