Message for Ron de Bruin

[Steph]

Hi Ron. Thanks for the help you have given me on a few questions I
had today. I now need to copy and paste data from several sheets
witin a workbook into a single master sheet. And low and behold, a
Google search on that topic brought me to your tips website!!

I have plagarized the following code from your site:

Sub Consolidate2()

Dim ws As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

Application.ScreenUpdating = False
Set DestSh = Worksheets("Consolidate")
For Each ws In Worksheets
If ws.Name <> DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)
sh.Range(sh.Rows(3), sh.Rows(shLast)).Copy
DestSh.Cells(Last + 1, 1)
End If
Next
Application.ScreenUpdating = True

End Sub

When I run it, I get a "Sub or function not defined" error on the word
LastRow. Did I screw something up?

Thanks again!!

-Steph

 

[Ron de Bruin]

Hi Steph

You forgot to copy the function also in the module
The fuction will find the last row with data

From my website
**************
Where do I place the code and the functions?

1. Alt-F11
2. Insert>Module from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs

http://www.mvps.org/dmcritchie/excel/getstarted.htm
See David McRitchie's site if you just started with VBA

'**************************************************************

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function