Hi Corey,

Try therefo

'=============
Private Sub Tester()
Dim WB As Workbook
Dim srcSH As Worksheet
Dim destSH As Worksheet
Dim srcRng As Range
Dim destRng As Range
Const sName As String = "MyNewSheet" '<<==== CHANGE

Set srcSH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE
Set srcRng = srcSH.Range("A1:D10") '<<==== CHANGE

Set WB = Workbooks.Open(Filename:= _
"C:\B\AA\Test2.xls") '<<==== CHANGE

With WB
Set destSH = .Sheets(.Sheets.Count)
Set destRng = destSH.Range("B1") '<<==== CHANGE
srcRng.Copy Destination:=destRng
.Close SaveChanges:=True
End With
End Sub
'<<=============


I do not understand:

To the above i am trying to have a MAX value of [I12] in ALL sheets,
displayed on the sheet that originally opens and this wil be the next
number to enter into the msg box above in Macro5
Not able to as yet, still manually doing this.

However, this should have no bearing on your original question or the
suggested code.

---
Regards,
Norman



"Corey" wrote in message
...
Code when opening
Private Sub Workbook_Open()
Macro5
End Sub


and


code for Macro5

Sub Macro5()
'
' Macro1 Macro
' Macro recorded 20/06/2006 by Corey
'
Dim sh As Worksheet
Dim msg As String, sName As String
msg = "Enter the Next Quote Number...."
Do
sName = InputBox(msg)
If sName = "" Then Exit Sub
On Error Resume Next
Set sh = Worksheets(sName)
On Error GoTo 0
msg = "Quote Number has been used, try again: "
Loop While Not sh Is Nothing
With ActiveWorkbook
.Worksheets("STQ Template").Copy After:=.Worksheets(.Worksheets.Count)
End With
ActiveSheet.Name = sName
[I12].Select
ActiveCell.Value = sName

'
End Sub




To the above i am trying to have a MAX value of [I12] in ALL sheets,
displayed on the sheet that originally opens and this wil be the next
number to enter into the msg box above in Macro5
Not able to as yet, still manually doing this.

Regards

Corey