Combine Worksheets - help with Code!

[Guest]

Hi all,
I need some assistance with combining worksheets. I have 89 worksheets that
have the same format (columns, headings) and need to combine these into one
large worksheet. The only difference is the actual data and the amt of
records on each.

I've looked thru the discussion boards and am trying to use code provided by
rondebruin, however when I run I receive a a compile error. Since I am by no
means a VBA expert, I have no idea what to do. Can someone look thru this
code and assist me? The field highlighted via the compile error is
LastRow(DestSh) --->see below.


Thanks in advance for any help!
Cheryl
===============================================
Sub Test2()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long

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

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
^^^^receive compile error - sub or function not
defined
shLast = LastRow(sh)

'This example copies everything, if you only want to copy
'values/formats look at the example below the first example
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last +
1, "A")

End If
Next

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[Ron de Bruin]

Hi CherylH

Read the information on the page good
http://www.rondebruin.nl/copy2.htm

You can find the functionand instructions on the bottom of the webpage