Copy sheet to workbook throwing runtime error 13 "Type Mismatch"

[Gingertrees]

No idea what changed, but now this gets stuck at the Copy after line. Throws Runtime Error 13: Type Mismatch.
Could someone let me know WHY and help me fix it??? Thanks in advance. :

Sub OpenCopyOrange()
'////Sub 1//////'////Sub 1//////'////Sub 1//////
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic


Dim tempfiletocopy As Variant  
Dim tempfileName As String
Dim tempsheet As Worksheet
Dim OrangeWb As Workbook


Set OrangeWb = ActiveWorkbook


tempfiletocopy = Application.GetOpenFilename
Workbooks.Open tempfiletocopy
tempfileName = ActiveWorkbook.Name
Set tempsheet = Workbooks(tempfileName).ActiveSheet
tempsheet.Name = "Orange"


Sheets("Orange").Copy After:=Workbooks(OrangeWb).Sheets(ThisWorkbook.Sheets.Count)
'////Info box appears stating: Workbooks(OrangeWb).Sheets(ThisWorkbook.Sheets.Count) = <type mismatch="">
'////
If Not SheetExists("Orange" & Sheets.Count) Then
ActiveSheet.Name = "Orange" & Sheets.Count
Else
ActiveSheet.Name = "Orange" & (Sheets.Count * 3)
End If
'///this prevents the "sheet name exists" error


Workbooks(tempfileName).Close savechanges:=False
'///closes file the new sheet was copied from


Application.ScreenUpdating = True
Application.DisplayAlerts = True
'//reset the speed tricks


End Sub

This is related to the code Norie helped me with recently (see http://www.mrexcel.com/forum/excel-...-end-copying-middle-second-place-instead.html). It worked then, but I've added and deleted other worksheets and certainly done other bad stuff since then.</type>

 

[Kuljack]

Add this back in above the broken line:

Sheets("Orange").Select

It adds to the code, but I've seen a similiar issue before and think this addition resolved it.

 

[Gingertrees]

Tried that - still throwing the same error at the same place. Other ideas?

 

[Kuljack]

Tried that - still throwing the same error at the same place. Other ideas?

Okay, ignore that addition. Are you working in two workbooks, if not it looks like your code should read this:

Sheets("Orange").Copy After:=Workbooks(tempfileName).Sheets(ThisWorkbook.Sheets.Count)

 

[JoeMo]

No idea what changed, but now this gets stuck at the Copy after line. Throws Runtime Error 13: Type Mismatch.
Could someone let me know WHY and help me fix it??? Thanks in advance. :

Sub OpenCopyOrange()
'////Sub 1//////'////Sub 1//////'////Sub 1//////
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic


Dim tempfiletocopy As Variant  
Dim tempfileName As String
Dim tempsheet As Worksheet
Dim OrangeWb As Workbook


Set OrangeWb = ActiveWorkbook


tempfiletocopy = Application.GetOpenFilename
Workbooks.Open tempfiletocopy
tempfileName = ActiveWorkbook.Name
Set tempsheet = Workbooks(tempfileName).ActiveSheet
tempsheet.Name = "Orange"


Sheets("Orange").Copy After:=Workbooks(OrangeWb).Sheets(ThisWorkbook.Sheets.Count)
'////Info box appears stating: Workbooks(OrangeWb).Sheets(ThisWorkbook.Sheets.Count) = <type mismatch="">
'////
If Not SheetExists("Orange" & Sheets.Count) Then
ActiveSheet.Name = "Orange" & Sheets.Count
Else
ActiveSheet.Name = "Orange" & (Sheets.Count * 3)
End If
'///this prevents the "sheet name exists" error


Workbooks(tempfileName).Close savechanges:=False
'///closes file the new sheet was copied from


Application.ScreenUpdating = True
Application.DisplayAlerts = True
'//reset the speed tricks


End Sub

This is related to the code Norie helped me with recently (see http://www.mrexcel.com/forum/excel-...-end-copying-middle-second-place-instead.html). It worked then, but I've added and deleted other worksheets and certainly done other bad stuff since then.</type>

OrangeWb is a workbook, not a workbook name. Change this:

Sheets("Orange").Copy After:=Workbooks(OrangeWb).Sheets(ThisWorkbook.Sheets.Count)

to this:

Sheets("Orange").Copy After:=OrangeWb.Sheets(Sheets.Count)

 

[Gingertrees]

aha! Rookie mistake on the name vs wb. That fix worked. Thanks Joe

 

[JoeMo]

aha! Rookie mistake on the name vs wb. That fix worked. Thanks Joe

You are welcome - thanks for the reply.