Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet containing names to other workbook w/o problems? | Excel Discussion (Misc queries) | |||
Creating Reports in another worksheet in a workbook | Excel Discussion (Misc queries) | |||
CREATING NEW WORKBOOK FILE EVERY MONTH AND NEW WORKSHEET EVERY DATE | Excel Programming | |||
Problems with Shared worksheet/workbook | Excel Programming | |||
creating new workbook from single worksheet | Excel Programming |