Hi Ron,

Been doing lots of thinking and trying, and almost there but not
quite.... I am using the macro listed below, (your Macro from Yesterday)
and all I want is to be able to copy another cell reference (b5) in
addition to the range specified (which is b9:b20). It can be in the last
column...

Thanks, appreciate it...

Regards

Darin

Sub Test1()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)

sh.Range("b9:f20").Copy DestSh.Cells(Last + 1, "A")
'Instead of this line you can use the code below to copy
only the values
'or use the PasteSpecial option to paste the format
also.


'With sh.Range("A1:C5")
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


'sh.Range("B10:h20").Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

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

End If
Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
End Sub





*** Sent via Developersdex http://www.developersdex.com ***