Merging worksheets together into a wordbook

[T3l3cm0:123]

Im trying to merge worksheets together into one workbook. I have a work book that has over 20 sheets. I has looking to create a macro that would merge sheets together (deepening on their name) into a separate workbook.
So far this is the code that I have got, it mergers all the sheets in the workbook together but I would like to merge them by name.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Sub mergedata()Sheets(1).Activatelastrow = ActiveSheet.UsedRange.Rows.CountFor Each Sheet In SheetsIf Sheet.Index <> 1 ThenRowCount = Sheet.UsedRange.Rows.CountSheet.UsedRange.Copy Destination:=Sheets(1).Cells(lastrow + 1, 1)lastrow = lastrow + RowCountSheet.UsedRange.ClearEnd IfNext SheetEnd Sub</code></pre>

 

[b.downey]

I am unclear on you requirements. However, here is a modification to you code that will allow you to specify which sheets should be merged into sheet(1). Simply add the sheet names to the "Case" Line in the following code:

Sub mergedata()
    Sheets(1).Activate
    lastrow = ActiveSheet.UsedRange.Rows.Count
    For Each Sheet In Sheets
        Select Case LCase(Sheet.Name)
            Case "sheet2", "sheet5"
                RowCount = Sheet.UsedRange.Rows.Count
                Sheet.UsedRange.Copy Destination:=Sheets(1).Cells(lastrow + 1, 1)
                lastrow = lastrow + RowCount
                Sheet.UsedRange.Clear
        End Select
    Next Sheet
End Sub