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

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CherylH" <(E-Mail Removed)> wrote in message news:70EDC478-31D3-41D5-A654-(E-Mail Removed)...
> 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
>
>