Sheet Position

[James Weaver]

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

Sheets("abc").Copy Before:=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

 

[Don Guillett]

Try
sheets("End")

 

[pfsardella]

Is this what you're trying to do?

Sheets("abc").Copy Before:=Sheets(Sheets.Count)

HTH
Paul

 

[Bernie Deitrick]

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

 

[Jake Marx]

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