Sub AddSheet()
Dim i As Long
Dim LastRow as Long

i = Worksheets.Count
Application.ScreenUpdating = False

Sheets(i).Copy Befo=Sheets(i)
ActiveSheet.Name = Range("h6").Value
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
Range("H8:I" & LastRow).Clear
Range("H6").ClearContents
WorkSheets(1).Activate

FillBalanceSheets

Application.ScreenUpdating = True
End Sub

However, I am not
able to type text in the cell and then press the button.
You are still in cell edit mode. Code cannot run until you have completed
the cell data entry by pressing Enter or clicking another cell. You really
don't want to invoke a macro on this sheet by pressing Enter. The code would
be in a WorkSheet change Event, and would run every time you changed cells
anywhere on the sheet. A big drain on memory and performance. Stick with the
button.

wrote in message
ups.com...
Perfect!

How can I remove the contents of the H6 cell but not the formating?

I currently have a button assigned to call addsheet. However, I am not
able to type text in the cell and then press the button. I first have
to choose something outside of the cell. How can I make it so I type
in the cell and press enter which will invoke the macro?

Thanks.