It looks like the CopyRange and CopyRangeValues subroutines both do about the
same thing. Each combines all the existing data on all the other worksheets and
puts it onto a Worksheet called master.

But the CopyRange version copies the cells (including formulas and formats),
while the copyrangevalues just takes the values from each sheet (avoiding
formulas and formats).

So pick the one you want and delete the other.

Then, since they each rebuild the master worksheet, you can just delete the
existing master worksheet first. Then run the macro.

Try this against a copy of your workbook--just to make sure it does what you
want.

===
Knowing which sheet was new and when to copy that data may be more difficult
than just recreating the master worksheet.

But as an alternative, you could just copy the range to the bottom of the master
worksheet when you want.

If you record a macro when you try this, your code could be very close to done.

Post back if you need help making it more general.

jonesaa05 wrote:

Hi,

I got this code from the net, but I wish to modify it so that it can
add data to the exsiting Master sheet whenever a new worksheet has been
added to the exsiting workbook which already contain other sheets. I
also try with the Function LastRow but it didn't give the results I
want. I hope some experts can please kindly help to take a
look to modify the codes. Any similar ideas also accepted.

Thankyou very much for any guides.

The codes are as below:

Option Explicit

Sub CopyRange()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
If sh.UsedRange.Count 1 Then
Last = LastRow(DestSh)
sh.Range("A1:C5").Copy DestSh.Cells(Last + 1, 1)
End If
End If
Next
Application.ScreenUpdating = True
End Sub

Sub CopyRangeValues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
If sh.UsedRange.Count 1 Then
Last = LastRow(DestSh)
With sh.Range("A1:C5")
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).Value = .Value
End With
End If
End If
Next
Application.ScreenUpdating = True
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

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

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(Sheets(SName).Name))
End Function

--
jonesaa05
------------------------------------------------------------------------
jonesaa05's Profile: http://www.excelforum.com/member.php...o&userid=28021
View this thread: http://www.excelforum.com/showthread...hreadid=475287

--

Dave Peterson