Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy a worksheet - but not the value (text)
I have a worksheet that has all the formulas and formating I need, I close it
out and start a new worksheet the end of the month. What I want to do is make a copy of that worksheet WITHOUT copying the data, Just the format and formulas. (Don't want the names and addresses, or the amounts etc...) I insert a work sheet, I copy the old one then when I go to paste, but the options I want - Paste all EXCEPT value are not available. Using the other choices Paste all the data. When I click on just formulas - I still get all the data, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy a worksheet - but not the value (text)
Buddy
A non programming method would be to delete the data and save the workbook as a template (.xlt) file e.g. SalesTemplate.xlt and open this up at the beginning of the month. Save the template as say JanSales05.xls and enter work normally. Peter Atherton "buddyorliz" wrote: I have a worksheet that has all the formulas and formating I need, I close it out and start a new worksheet the end of the month. What I want to do is make a copy of that worksheet WITHOUT copying the data, Just the format and formulas. (Don't want the names and addresses, or the amounts etc...) I insert a work sheet, I copy the old one then when I go to paste, but the options I want - Paste all EXCEPT value are not available. Using the other choices Paste all the data. When I click on just formulas - I still get all the data, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy a worksheet - but not the value (text)
Hello Buddy, Here is macro that automatically adds a New Worksheet to the Workbook and copies only the Cell Formats and Formulas that have been used on the the Active Worksheet. No need to select the range to be copied. Copy and Paste this code into a VBA standard Module. You can run the macro by pressing the Keys *ALT and F8* to bring display the Macro List. Select "CopyWorksheet" and click Run. Code: -------------------- Public Sub CopyWorksheet() Dim UsedRange As Range Dim NewCell As Range Dim NewWks As Worksheet ActiveWorkbook.Worksheets.Add Set NewWks = Worksheets(Worksheets.Count) Set UsedRange = ActiveSheet.UsedRange For Each Cell In UsedRange With Cell .Copy Set NewCell = NewWks.Range(.Address) End With With NewCell .PasteSpecial (xlPasteFormats) .PasteSpecial (xlPasteFormulas) If Not Cell.HasFormula Then .Value = "" End With Next Cell Application.CutCopyMode = False End Sub -------------------- -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=380414 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy text from one worksheet to another | Excel Discussion (Misc queries) | |||
I need to copy a text value from one worksheet to another | Excel Discussion (Misc queries) | |||
Copy text from text box to cell in another worksheet | Excel Worksheet Functions | |||
Can I copy everything EXCEPT text from one worksheet to another sh | Excel Worksheet Functions | |||
How can I copy text from one worksheet to another | Excel Worksheet Functions |