Sheet Position
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 |
Sheet Position
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 |
Sheet Position
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 |
Sheet Position
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 |
Sheet Position
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 . |
All times are GMT +1. The time now is 10:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com