Merge multiple Sheets into ONE

[luis.carrion]

is there a way to merge multiple sheets into one sheet, i have from sheet1 thru sheet95

 

[VoG]

This should help you http://msdn.microsoft.com/en-us/library/cc793964.aspx

 

[justme1122]

ops, did read the question rite

 

[luis.carrion]

i try but not working

 

[VoG]

In what way not working?

 

[luis.carrion]

when i run the code it comeback blank.

 

[VoG]

Try changing the line in red

Sub CopyRangeFromMultiWorksheets()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim CopyRng As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' 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("A1:G1")
            Set CopyRng = sh.UsedRange

 

[luis.carrion]

Cant you give me a sample of what supposed to go

Set CopyRng = sh.UsedRange ()

 

[VoG]

The original was

Set CopyRng = sh.Range("A1:G1")

Change that to

Set CopyRng = sh.UsedRange

 

[luis.carrion]

it gives me a message

compile error
sub or function not define

and it highlights the red below

Last = LastRow(DestSh)