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


"Norman Jones" wrote in message
...
Hi Corey,

In order to copy to the newly crated sheet, it is necessary to know either
the name of the new sheet or its position.

IMO, the best option is to adopt my original suggestion, and create the
new sheet as part of the button code in the first workbook. Alternatively,
post the 2nd workbook open event code which creates the new sheet and I
will adapt the suggested current code.


---
Regards,
Norman



"Corey" wrote in message
...
Ok, thanx
I get a little way down the code before i get another error in:
Set destSH = .WB.Sheets("100101")
The "100101" is a value i placed in there, but assuming that it refers to
the Newly created sheet in wb2.
I have another macro that creates this and a msg box that gives the sheet
name value.
Can this be adapted to suit somehow?

Regards

Corey

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