moving blank tabs

hi folks

my financial reporting software can produce reports with what it calls trees. Each branch of a tree can be a sub set of the database. Some branchs of the tree are blank. The software can also send reports to excel. In the spreadsheet you get one sheet or tab for each branch of the tree. the software is not able to not export a blank branch of the tree.

is there something in excel that can seek out the blank tabs and move them to the end of the workbook?

..yes, blank tabs can be moved to the end of the workbook, or deleted if preferred.
I presume the blank tabs have sheet names but 'no data' on the sheets themselves?
If this is the case, a short macro (vba routine) could be created to do what you want, and assigned to a keyboard-shortcut.

Let us know what defines a 'blank tab' and we'll show you how to do it.

Option Explicit
Sub MoveBlankSheets()
Dim iShtCount As Integer
Dim i As Integer
iShtCount = Worksheets.Count
For i = iShtCount To 1 Step -1
If Application.WorksheetFunction. _
CountA(Worksheets(i).Cells) = 0 Then
Worksheets(i).Move after:=Worksheets(iShtCount)
End If
Next
End Sub

Try the following code. It will move any sheet with no data to the end.

HTH,
Maud

Code:

Sub Macro1()
On Error GoTo Errorhandler
For I = 1 To Worksheets.Count
If Sheets(I).UsedRange = "" Then
Sheets(I).Select
Sheets(I).Move After:=Sheets(Worksheets.Count)
End If
Errorhandler:
Next I
End Sub

Maudibe,
You need to go from Worksheets.Count to 1 Step -1 or you will miss worksheets. After any sheet is moved the next sheet will not be tested

For example, If both Sheets(1) and Sheets(2) are blank, your code will move sheets(1) to the end, then increment I to 2, but after moving sheets(1) it becomes sheets(N), and sheets(2) will become sheets(1), so will be skipped as you will look at the new sheets(2) [Which was the former sheets(3)] it will remain at the start.

[Probably not a problem with this example, but it is a bad practice and a bad habit to get into. It is better to use Sheets.count with sheets(I) or use worksheets.count with worksheets(I) to maintain consistency. Having chartsheets will screw up the count. You loop through the worksheets.count (which does not include a count of any chartsheets), but you use Sheets(I) which includes chart sheets.]

As an alternative, the following code examines the range between the header and footer of each sheet for data. If it is blank, it moves it to the end. The code assumes that you have a 3 row header and the footer at the bottom has at least some data in column A else change the column where indicated.

Code:

Sub MoveSheet()
Dim Rng As Range
For I = 1 To Worksheets.Count
LastRow = Worksheets(I).Cells(Rows.Count, 1).End(xlUp).Row 'CHANGE THE 1 TO A COLUMN THAT HAS
'CONSISTANT DATA FOR THE FOOTER
Set Rng = Worksheets(I).Rows("4:" & LastRow - 1)
If WorksheetFunction.Aggregate(3, 4, Rng) <> 0 Then
Worksheets(I).Move Before:=Sheets(1)
End If
Next I
End Sub