You can get the tab names in cells using the CELL function. If you prefer to use a user-defined function, you can try something simple, like this function:

Function TabName1() As String
Application.Volatile
TabName1 = ActiveSheet.Name
End Function

This function won’t provide the desired outcome, however, because it always returns the name of the active worksheet. That means that if you have the function called on each of the sheets in your workbook, it will always return the name of the active sheet on each of those worksheets, instead of the name of the sheet on which the function is used. The following function provides better results:

Function TabName2() As String
Application.Volatile
TabName2 = Application.Caller.Parent.Name
End Function

If you think you’ll want to use the function to refer to a worksheet name elsewhere in the workbook, then this function will work better for you:

Function TabName3(cell As Range)
TabName3 = cell.Worksheet.Name
End Function
This version of the function requires that you provide a cell reference—any cell reference—to a cell on the worksheet whose name you want to use.

Of course, if you would rather not use a user-defined function, you could simply create a macro that would stuff the name of each worksheet tab into the same cell in each worksheet. For instance, the following macro steps through each of the worksheets in the workbook and places the name of each worksheet into cell A1.

Sub TabName4()
For J = 1 To ActiveWorkbook.Sheets.Count
Sheets(J).Cells(1, 1).Value = Sheets(J).Name
Next
End Sub

You should note that this approach is not dynamic (it needs to be rerun each time you change worksheet names or add new worksheets). It also overwrites anything that is in cell A1. (If you want the worksheet names placed in a different cell on each worksheet, change the values used in the Cells collection.)

My approach to deleting blank rows would be to use the data filter. However if you like to do things in code (VBA), I have seen this macro to delete rows.
Gary L Brown wrote it
‘/============================/
‘ Sub Purpose: Delect all blank ROWS within the active cell’s
‘ Used Range
‘
Public Sub DeleteBlankRows()
Dim dbMaxRow As Double, dbMinRow As Double, i As Double
Dim dbMaxCol As Double
Dim rng As Range

On Error Resume Next

‘only look in used area of the worksheet where active cell is
Set rng = Selection.Parent.UsedRange

‘calculate area to be searched for blank rows
dbMaxRow = rng.Rows.Count ‘# of rows in used area
dbMinRow = rng.Cells(1, 1).Row ‘1st row in used area
dbMaxCol = rng.EntireColumn.Count ‘# of columns in used area

For i = dbMaxRow To dbMinRow Step -1
If IsError(rng.Cells(1, 1).Offset(i – 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count) Then
Else
If rng.Cells(1, 1).Offset(i – 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then
rng.Cells(1, 1).Offset(i – 1, 0).EntireRow.Delete
End If
End If
Next i

Set rng = Nothing

End Sub
‘/============================/

This is great when you have a report you want to email on a regular basis. It is VBA code but you don’t need to know how it works, just how to adapt it to your needs.

First, Open the file you want to send reports from. Copy the code below and then press Alt+F11. This brings up the VBA editor. Paste all the code into a new module. You can run the macro from the Tools menu or create a button to run it.
I have created an example for you to play with. Click on the title above to see all the code.

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007  Read more »