Copy worksheet, trying to paste to new workbook error assistance.

[fishy]

I have a workbook that calls the following sub to copy the calculated sheet
and renames it based on the range within the cell.

The core code seems to work but every so often it hiccups and tries to copy
the 'summary' sheet to a new book rather than remain in the source file
'Buzz.xls'

Attached my code. Is theer any way of telling it that it must copy into the
buzz workbook?

Sub Copy_TeamFile()

Sheets("Summary").Select

'copy sheet
Sheets("Summary").Select
Sheets("Summary").Copy After:=Worksheets(Worksheets.Count)
Sheets("Summary (2)").Select

'Refresh data
Call Calc_Update

'paste values
Cells.Select
Range("A7").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Rename sheet to team name
ActiveSheet.Name = Range("B2").Value

'Repeat for every team if neccesary
Sheets("Control").Select
Range("A1").Select

End Sub

 

[JLGWhiz]

Give this a try:

Sub Copy_TeamFile()

Set newSht = Sheets("Summary").Copy(After:=Worksheets(Worksheets.Count))
'Refresh data
Call Calc_Update
'paste values
newSht.Cells.Copy
newSht.Cells.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Rename sheet to team name
newSht.Name = Range("B2").Value

'Repeat for every team if neccesary
Sheets("Control").Select
Range("A1").Select

End Sub


I am not sure what Calc_Update does, so if there is a problem, you might
need to look at that macro also.