Prev: Cell referencing in Macros
Next: how to close Read Only file without prompt to save copy
From: fishy on 6 May 2010 06:22
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
From: JLGWhiz on 6 May 2010 10:16
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.



"fishy" <fishy(a)discussions.microsoft.com> wrote in message
news:D0700967-766F-4301-AC3E-2709FE00A50D(a)microsoft.com...
>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


 | 
Pages: 1
Prev: Cell referencing in Macros
Next: how to close Read Only file without prompt to save copy