saving only the worksheet
Posted by suzanne on January 11, 2002 9:29 AM
i have to do costing, i have three macros copying data from three diffrent sheets onto the the first sheet named cost sheet. I have to save the costing sheet for each product made, to save space on the floppy disk I delete those three sheets and then one by one i delete the macros before saving, this is ok, but is i do costing for 20 to 30 items it becomes tedious; is there a macro or a program i can use to do this automatically without taking up to much space on the floppy?
Posted by Richard Winfield on January 11, 2002 9:57 AM
The following macro will copy the active worksheet to another workbook containing only one sheet and will then save it with a filename based on the value in a certain cell. Using this method my file sizes are usually in the range of 35-40K, compared to saving the entire workbook which resulted in a file size of 275-300K.
The easiest way to run this is to create a button on the toolbar and assign the macro to it. If you save this macro in the Personal.XLS file then it is available any time you start Excel. To do this open the file Personal.xls and right click on the sheet tab, choose view code, then paste this macro into the VB editor. Close, then save the changes to personal.xls.
Sub savesheet()
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("d8").Value (This is the cell I reference to get the sheet name. Modify as needed)
On Error GoTo do_not_overwrite(This is if a duplicate file name exists. Answer NO to the Excel message "This filename already exists. Do you want to overwrite?")
ActiveSheet.SaveAs Filename:="C:\YOUR DIR\" & ThisFile & ".xls" (Change YOUR DIR to the directory you want the file saved to)
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Exit Sub
do_not_overwrite:
MsgBox "The filename already exists. (ADD WHATEVER COMMENT TO THE USER HERE)"
Application.DisplayAlerts = False
ActiveWorkbook.Close(Closes the copy of the worksheet without saving and returns the user to the original worksheet to modify the file name)
Application.DisplayAlerts = True
End Sub
Hope this helps,
Rick
