Create new sheet on a specific tab

[cccbzg]

Hi,

This code combines all of the worksheets in a workbook and creates a summary sheet. It works fine but the summary sheet winds up in the middle (sort of) of the other tabs. Not only don't I know why it winds up there, I can't quite figure out how to place the new sheet on a particular tab, like Sheet1.

Any suggestions?

I'm always so impressed with and appreciative of the help I get from this forum.

Many thanks,
Bonnie


' Delete the summary sheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

' Add a new summary worksheet.
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

' Loop through all worksheets and copy the data to the
' summary worksheet.
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then

' Find the last row with data on the summary worksheet.
Last = LastRow(DestSh)

' Specify the range to place the data.
Set CopyRng = sh.Range("A140:EO140")

' Test to see whether there are enough rows in the summary
' worksheet to copy all the data.
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the " & _
"summary worksheet to place the data."
GoTo ExitTheSub
End If

' This statement copies values and formats from each
' worksheet.
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'
' originating Sheet name in the H column.
DestSh.Cells(Last + 1, "EQ").Resize(CopyRng.Rows.Count).Value = sh.Name

End If
Next

 

[AlphaFrog]

To create the new sheet as the 1st sheet, change this...

Set DestSh = ActiveWorkbook.Worksheets.Add(Before:=Sheets(1))

 

[Rick Rothstein]

This code combines all of the worksheets in a workbook and creates a summary sheet. It works fine but the summary sheet winds up in the middle (sort of) of the other tabs. Not only don't I know why it winds up there, I can't quite figure out how to place the new sheet on a particular tab, like Sheet1.

Any suggestions?

    ' Add a new summary worksheet.
    Set DestSh = ActiveWorkbook.Worksheets.Add[B][COLOR="#FF0000"](Before:=Sheets(1))[/COLOR][/B]
    DestSh.Name = "RDBMergeSheet"

Add the part I show in red above to place the sheet at the beginning of the sheets.

 

[cccbzg]

My thanks to both Rick and AlphaFrog. That worked like a charm!
It's a good to know little tidbit.

Always grateful,
Bonnie