On Oct 24, 2:28 pm, "Ron de Bruin" wrote:
Hi SangelNet

There are 3 sheets with data(V) in one cell in row
65338
65246
65399

So your range is to big to copy to one sheet
Use Ctrl-end on each sheet and you will find your last cell

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ron de Bruin" wrote in .. .

Send me the workbook private then i take a look

--

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

"SangelNet" wrote in oglegroups.com...
On Oct 24, 11:37 am, "Ron de Bruin" wrote:
Then I think that there is a empty sheet in your workbook

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"SangelNet" wrote in oglegroups.com...
Yes Sir, I added the lastrow function. The code im using at this point
and getting error is this:

Sub merge()
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 And sh.Name < "maindata" Then
Last = LastRow(DestSh)
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
__________________________________________________ _________________________________________
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

No, No blanks. Actually now its merging just 2 of the sheets and then
giving the error. tried doing it with new clean sheets, still.
I will keep trying.

Ron

That definitely was it.
Thnx so much, you've been very kind.

Thnx also for the great info on your page.