Duplicating a worksheet minus the data

N

NavyPianoMan

I can't figure out how to duplicate a worksheet minus the data (so I can
insert new data using all the same formulas). In other words, I want to copy
the formulas, formatting, column&row titles, but NOT the data.
 
A

Anne Troy

The easiest way for ME to do this is to save the file to a new name and
delete all the data. Then save the file again. Of course, don't delete the
formulas! :)
 
M

MartinW

Hi,

One way.
Copy the worksheet by right click on the sheet tab and
select Move or Copy.

In the new sheet hit F5 to bring up the GoTo Dialog Box.
Click Special
Check Constants
Uncheck Text, Logicals and Errors, leave Numbers checked
Click OK
Tap delete and you are done

HTH
Martin
 
D

Dave Peterson

And to expand on Anne's suggestion...

After you create the new workbook
Select the range of values you want cleared--don't include titles,
instructions--or anything that shouldn't be cleared. But you can include
formulas.

Then hit Edit|Goto|special|Constants
click ok
hit the delete key to clear that subset of cells.
 
F

FSt1

hi,
yet another way. macro....
Sub deltest()
ActiveWorkbook.Save
Range("A1:K25").Copy 'change to fit your data
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Range("B3:B5,D3:D5,B9,D9").ClearContents
'change to all ranges and cells you want to delete
msgbox("Done!")
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Regards
FSt1
 
N

NavyPianoMan

That's cool. The problem is, I wanted to keep some numbers: social security
numbers. How can I delete all numbers EXCEPT the socials?
 
M

MartinW

About the only way I can think of is if the social security
were entered as text instead of numbers.

You may be able to convert them with the TEXT function,
i.e. =TEXT(A1,"000") and copy>paste special>values
on the result to get rid of the formulas.
Although that could get a bit messy unless your numbers
are all in one column.

HTH
Martin
 
Top