Sheet Position

  • Thread starter Thread starter James Weaver
  • Start date Start date
J

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
 
Is this what you're trying to do?

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

HTH
Paul
 
James,
Also, do you know how to check whether a sheet already
exists?
Click to expand...

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
 
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
 
Back
Top