Duplicating a worksheet minus the data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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! :)
 
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
 
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.
 
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
 
That's cool. The problem is, I wanted to keep some numbers: social security
numbers. How can I delete all numbers EXCEPT the socials?
 
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
 
Back
Top