I know that code can be written to reference sheets
according to their position in the Workbook, e.g.:

Sheets("abc").Copy Befo=Sheets(6)

The sheet that is in position 6 is called "End" but it
moves position as you copy more sheets into the file! So,
I need some code to be able to locate the numerical
position of the "End" sheet and put the variable back into
the 'copy' code above.

Any ideas anyone?!

Thanks,
James

Try
sheets("End")

"James Weaver" wrote in message
...
I know that code can be written to reference sheets
according to their position in the Workbook, e.g.:

Sheets("abc").Copy Befo=Sheets(6)

The sheet that is in position 6 is called "End" but it
moves position as you copy more sheets into the file! So,
I need some code to be able to locate the numerical
position of the "End" sheet and put the variable back into
the 'copy' code above.

Any ideas anyone?!

Thanks,
James

Is this what you're trying to do?

Sheets("abc").Copy Befo=Sheets(Sheets.Count)

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

I know that code can be written to reference sheets
according to their position in the Workbook, e.g.:

Sheets("abc").Copy Befo=Sheets(6)

The sheet that is in position 6 is called "End" but it
moves position as you copy more sheets into the file! So,
I need some code to be able to locate the numerical
position of the "End" sheet and put the variable back into
the 'copy' code above.

Any ideas anyone?!

Thanks,
James

James,


Also, do you know how to check whether a sheet already
exists?


Try to read a sheet property, and trap the error. Example:

Sub TryNow()
Dim myName As String
Dim myTest As String
On Error GoTo NoSheet
myTest = "TestSheet"

myName = Worksheets(myTest).Name
MsgBox "Sheet " & myTest & " exists"
Exit Sub

NoSheet:
MsgBox "Sheet " & myTest & " does not exist."
End Sub


--
HTH,
Bernie

Hi James,

Here's a function to determine if a sheet exists:

Public Function gbSheetExists(rsShtName As String, _
Optional rwbWorkbook As Workbook) As Boolean
On Error Resume Next
If rwbWorkbook Is Nothing Then
gbSheetExists = Len(Sheets(rsShtName).Name)
Else
gbSheetExists = Len(rwbWorkbook.Sheets(rsShtName).Name)
End If
End Function

--
Regards,

Jake Marx
www.longhead.com

James Weaver wrote:
Great - thanks.

Also, do you know how to check whether a sheet already
exists?

James
-----Original Message-----
Try
sheets("End")

"James Weaver" wrote in message
...
I know that code can be written to reference sheets
according to their position in the Workbook, e.g.:

Sheets("abc").Copy Befo=Sheets(6)

The sheet that is in position 6 is called "End" but it
moves position as you copy more sheets into the file! So,
I need some code to be able to locate the numerical
position of the "End" sheet and put the variable back into
the 'copy' code above.

Any ideas anyone?!

Thanks,
James


.