save only 1 sheet from a multisheet workbook

Microsoft Excel 2000
August 21, 2010 at 12:20:04
Specs: Windows XP, 1 gb
i have an excel workbook with 4 sheets. 1 sheet for data input as well as result. the other 3 for calculation etc. i am using a 'save as' macro which will save the file with a cell value as its name in some desired location.
now, what i want that when i use that 'save as' macro, only the one sheet will be saved instead of all 4 and also it will delete all macros and formulas (only the values remain)
can any one help me?

See More: save only 1 sheet from a multisheet workbook

August 22, 2010 at 05:37:17

Here is a macro that you can incorporate into your existing macro.

It creates a one-worksheet workbook, consisting of the worksheet that you wish to copy & save.

All the cells on the worksheet in the new workbook are copied and then pasted back using PasteSpecial with Values and Formats.
This will remove all formulas and links from the new worksheet.

A macro cannot change/erase macros, but if you organize your original workbook so that there is no Visual Basic code attached to the worksheet that is going to be saved, you should not have any macros embedded in the new one-worksheet workbook.

The code goes on to save the workbook using data in cells A1 and A2. I did this just to test the concept - you already have the code required to do this.

Sub SaveWS()
Dim wbNew As Workbook

'copy one worksheet in current workbook, to a new workbook
'create an object to refer to the new workbook
Set wbNew = Workbooks(Workbooks.Count)

With wbNew
    'select all the cells on the worksheet in the new workbook
    'and Copy - PasteSpecial - Values & Formats - to remove any formulas
    .Worksheets(1).Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    'save new workbook using path in cell A1 and name in cell A2
    .SaveAs Filename:=.Worksheets(1).Range("A1") & .Worksheets(1).Range("A2")
    'close new workbook
End With
End Sub


Report •

August 23, 2010 at 10:27:02
it works buddy, thx


Report •
Related Solutions

Ask Question