How do you create a new BLANK worksheet with same cell format as another?

[Salmon Egg]

Suppose I have a worksheet formatted the way I like it. In particular,
it takes some effort to get column widths adjusted and various cells to
display using various number formats. If I have such a sheet as an
exemplar, how can I create another sheet just like that except that
there is no data in it. If I copy the original sheet and Paste Special
Format, the format of column width does not transfer.

I know I can save a copy of the workbook or worksheet and then open it.
I then can clear the data in the new sheet and rename it. Is there a
simpler and more direct way?

--

Sam

Conservatives are against Darwinism but for natural selection.
Liberals are for Darwinism but totally against any selection.

 

[Rick Rothstein]

How about just copying the sheet and then deleting all the content? Assuming
your "favorite" sheet is Sheet1...

Sub CopyFavoriteSheet()
Worksheets("Sheet1").Copy After:=Worksheets(Sheets.Count)
With Sheets(Sheets.Count)
.Name = "New Sheet Name"
.Cells.ClearContents
End With
End Sub

Rick Rothstein (MVP - Excel)

 

[Gord]

If you want this to be the default new worksheet for Excel you can
build one as you like then save it as a Template(*.xlt or xltx)

Name it SHEET.xlt or SHEET.xltx

Store it in your XLSTART folder and that will be New Worksheet when
one is added to a workbook.

You can do same for default workbook.

Name it BOOK


Gord Dibben Microsoft Excel MVP

 

[Salmon Egg]

If you want this to be the default new worksheet for Excel you can
build one as you like then save it as a Template(*.xlt or xltx)

Name it SHEET.xlt or SHEET.xltx

Store it in your XLSTART folder and that will be New Worksheet when
one is added to a workbook.

You can do same for default workbook.

Name it BOOK


Gord Dibben Microsoft Excel MVP

AND

How about just copying the sheet and then deleting all the content? Assuming
your "favorite" sheet is Sheet1...

Sub CopyFavoriteSheet()
Worksheets("Sheet1").Copy After:=Worksheets(Sheets.Count)
With Sheets(Sheets.Count)
.Name = "New Sheet Name"
.Cells.ClearContents
End With
End Sub

I have not worked with macros for a long time, When I last did, it was
with Excel Macro Language before visual Basic was popular. I realize the
methods described above can work, but in my mind, these methods are
relatively complicated. How would you be able to use the various Menu
commands directly without having to start a macro or saving a template.

To give a simple and specific example, suppose I have a list of checks I
made out for 2010. I want to prepare a new list for 2011.

Early rows: Headers for columns and other initial data
A Date of transaction
B Description of transaction
C Check mark to indicate posting or clearance
D Deposit amount
E Check amount
F Running balance
G Running cleared balance
And so on. All the cells for 2010 are formatted the way I want them to
be.

I now create a new worksheet. It will have column of standard width and
with general formats. I would like to copy the 2010 worksheet. Then I
would like to use Paste Special to transfer ALL the format information,
including column width and borders, to the 2011 worksheet. Paste Special
only seems to transfer formats Microsoft considers pertinent. Why must I
use a macro to do what I want?

--

Sam

Conservatives are against Darwinism but for natural selection.
Liberals are for Darwinism but totally against any selection.

 

[Rick Rothstein]

To give a simple and specific example, suppose I have a list of checks I

made out for 2010. I want to prepare a new list for 2011.

Early rows: Headers for columns and other initial data
A Date of transaction
B Description of transaction
C Check mark to indicate posting or clearance
D Deposit amount
E Check amount
F Running balance
G Running cleared balance
And so on. All the cells for 2010 are formatted the way I want them to
be.

I now create a new worksheet. It will have column of standard width and
with general formats. I would like to copy the 2010 worksheet. Then I
would like to use Paste Special to transfer ALL the format information,
including column width and borders, to the 2011 worksheet. Paste Special
only seems to transfer formats Microsoft considers pertinent. Why must I
use a macro to do what I want?

Okay, that is a little different than what I imagined you wanted. What about
this? Right click the name tab at the bottom of any sheet and select "Move
or Copy" from the popup menu that appears. On the dialog box that appears,
select the sheet name that you want to "duplicate" from the list and put a
checkmark in the "Create a copy" checkbox located under the list, then press
OK. You will get an exact copy of the worksheet you selected from the
list... rename the worksheet, then select everything on the worksheet that
you do not want and press the Delete key. I think this will leave you with
what you are after.

Rick Rothstein (MVP - Excel)

 

[Gord]

Copy the entire worksheet, not the rows and columns.

Select worksheet tab and right-click>move or copy>create a copy.

This replicates the worksheet exactly.

Now clear the data using Edit>Go To>Special>Constants.


Gord