Renaming a sheet copied from another workbook, errors ensue...

[Gingertrees]

Gaaah! Working on a macro to copy in a sheet from a daily report, into my "processing" workbook. The daily report worksheet to-be-copied will always be called "orange", so I figured when I copy it, I'll rename it "orange" + the number of sheets in the workbook (orange5 when sheets.count=5, orange 11 when sheets.count=11, etc.)
Unfortunately, I continue to get the Runtime error 438: Object doesn't support this property or method.

What am I doing wrong???

Sub OpenCopyOrange()


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic


Dim tempfiletocopy As Variant
Dim tempfileName As String


Dim sheet As Worksheet


'//open downloaded floor schedule
tempfiletocopy = Application.GetOpenFilename
Workbooks.Open tempfiletocopy
tempfileName = ActiveWorkbook.Name


Sheets("ORANGE").Copy After:=Workbooks("ORANGEA.xlsm").Sheets(Sheets.Count)


'///!/!/Here is where it errors!/!/!/!/!/!/!/!/!/!/!/!/!/
ActiveSheet.Name = "Orange" & (Workbooks("ORANGEA.xlsm").Sheets(Sheets.Count))
'///!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/!/


Workbooks(tempfileName).Close savechanges:=False


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

If anyone has a cool "SheetExists" error handler, that does more than tell me "this sheet name exists - deal with it.", that would be helpful, too.

 

[JoeMo]

Try:

ActiveSheet.Name = "Orange" & Sheets.Count

here's a UDF you can use to test if a sheet name already exists:

Function SheetExists(shName As String) As Boolean
SheetExists = False
For Each sh In .Sheets
    If sh.Name = shName Then
        SheetExists = True
        Exit For
    End If
 Next sh
End Function

Example of use in your code:
If Not SheetExists("Orange" & Sheets.Count) then
ActiveSheet.Name = "Orange" & Sheets.Count
Else
'Some other name
End If
[/code]

 

[Gingertrees]

Sometimes, it's the simple fixes. That was perfect - thanks, Joe!

 

[JoeMo]

Sometimes, it's the simple fixes. That was perfect - thanks, Joe!

You are welcome - thanks for the reply.