Oops click send to fast

If there is no data on one of the sheets the you also have a problem

--

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


"Hile" wrote in message ...
Thank you so much for this post!!!! It just saved my life and about a days
work! :-)

I modified the code to fit my needs, but it will not run. It stops at the
following line:
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy

Can someone tell me what I did wrong? Here's the modified code:
Sub MergeSheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

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

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

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

'loop through all worksheets and copy the data to the DestSh
For Each sh In Sheets(Array("Start", "End"))
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies values and formats from A2 onward
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False
Application.CutCopyMode = False
End With

'This will copy the sheet name in the H column if you want
'DestSh.Cells(Last + 1, "H").Value = sh.Name

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

Thanks again!
--
Hile


"Ron de Bruin" wrote:

Hi Rasoul

See
http://www.cpearson.com/excel/codemods.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Khoshravan" wrote in message ...
I am trying to copy a macro with a function from www.rondebruin.nl/copy2.htm
site.
It has explained how to enter the MAcro but not mentioned how/where to
insert the function. how should I do this? sorry for simple question but it
is my first time use function command in excel.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

where/how to insert a function


"Debra Dalgleish" wrote:

Ron de Bruin has code that will copy data from worksheets onto a master
sheet, and includes an option to insert the sheet name in a column.

http://www.rondebruin.nl/copy2.htm

Khoshravan wrote:
I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html