Creating a new workbook/worksheet problems
 

Hi,
I have a workbook that has a worksheet ("Standings") containing formulas
such as ='old stuff'!a1
where 'old stuff' is another worksheet. what i want to do is copy it but
instead of references, want to save the values such that if 'old stuff'!a1 is
2000, the new worksheet (Standings again) has that value not the formula.
What I currently have is

Sheets("Standings").Select
Sheets("Standings").Copy
ActiveWorkbook.SaveAs Filename:=standings

but this still creates "Standings" in the new workbook with references.
other than looping thru all the cells with
cells(x,y).value = cells(x,y).value
is there another way? pastespecial with xlpastevalues is basically what I
want to do.

another part to this.

if the new workbook already exists when i do the SaveAs, I do get prompted
if I want to overwrite it. If I say NO, I get a 1004 error. I guess I could
use On Error to trap it, determine that it is the 1004 and just exit. Is
there a better way?


--
Randy

Creating a new workbook/worksheet problems
 

Sheets("Standings").Select
Sheets("Standings").Copy
Activesheet.Cells.copy
Activesheet.Cells.PasteSpecial xlValues
ans = vbYes
if dir("Standings.xls") < "" then
ans = msgbox("Standings Exists, do you want to overwrite it?", _
vbyesno)
end if
if ans = vbYes then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="standings.xls"
Application.DisplayAlerts = True
End if

--
Regards,
Tom Ogilvy

"Randy" wrote in message
...
Hi,
I have a workbook that has a worksheet ("Standings") containing formulas
such as ='old stuff'!a1
where 'old stuff' is another worksheet. what i want to do is copy it but
instead of references, want to save the values such that if 'old stuff'!a1
is
2000, the new worksheet (Standings again) has that value not the formula.
What I currently have is

Sheets("Standings").Select
Sheets("Standings").Copy
ActiveWorkbook.SaveAs Filename:=standings

but this still creates "Standings" in the new workbook with references.
other than looping thru all the cells with
cells(x,y).value = cells(x,y).value
is there another way? pastespecial with xlpastevalues is basically what I
want to do.

another part to this.

if the new workbook already exists when i do the SaveAs, I do get prompted
if I want to overwrite it. If I say NO, I get a 1004 error. I guess I
could
use On Error to trap it, determine that it is the 1004 and just exit. Is
there a better way?


--
Randy