Richard

The 3 macros below do what you want. The first macro is a sheet
macro and must be placed in the sheet module for the "Summary" sheet. When
this macro fires, it will call the other 2 macros. Note that this macro
will fire every time you select the "Summary" sheet. Every time. To access
the "Summary" sheet module, right-click on the "Summary" sheet tab, select
View Code. Paste the first macro into that module. You may find it
somewhat of a nuisance for this macro to fire (and set off the other two)
every time you select the Summary sheet while you are setting up your file.
To prevent this macro from firing, access the Summary sheet module and
remark out all 3 lines of code.

The other two macros go in a standard module.

When you select the "Summary" sheet, the following will take place:

The used range of the "Summary" sheet will be cleared.

The code will figure out which sheet is for the current month (you must have
12 sheets named Jan, Feb, Mar, etc).

The data in this month's sheet will be copied to the "Summary" sheet as we
said before.

If Columns A:B of the "Summary" sheet (used range) exceeds row 30, the code
will snake the data into neighboring columns.

Note that the code will not insert a blank column between the snaked
columns. I didn't know if you wanted that or not. Come back if you want
that.

If you are unsure of where to put what macros, email me and I'll
send you a small file that has everything placed properly. My email address
is . Remove the "nop" from this address. HTH Otto





Private Sub Worksheet_Activate()

Call GetSummary

End Sub



Sub GetSummary()

Dim RngColB As Range

Dim i As Range

Dim Dest As Range

Application.ScreenUpdating = False

ActiveSheet.UsedRange.ClearContents

Set Dest = Range("A1")

With Sheets(Format(Date, "mmm"))

Set RngColB = .Range("B1", .Range("B" & Rows.Count).End(xlUp))

For Each i In RngColB

If IsEmpty(i) Then GoTo NextCell

Dest.Value = i.Row

Dest.Offset(, 1).Value = i.Value

Set Dest = Dest.Offset(1)

NextCell:

Next i

End With

Call SnakeSum

Application.ScreenUpdating = True

MsgBox "Summary is complete."

End Sub



Sub SnakeSum()

Dim HowMany As Long

Dim RngCopy As Range

Dim Dest As Range

HowMany = 30

If Range("A" & Rows.Count).End(xlUp).Row <= HowMany Then Exit Sub

Set Dest = Range("C1")

Set RngCopy = Cells(1, 1)

Do

RngCopy.Resize(HowMany, 2).Copy Dest

Set RngCopy = RngCopy.Offset(HowMany)

Set Dest = Dest.Offset(, 2)

Loop Until IsEmpty(RngCopy.Value)

Columns("A:B").Delete

End Sub

"Richard Walker" wrote in message
...
Thanks Otto, this strips and arranges the data quite well.

I was curious if it would be possible to tweak the way this works a bit.
I
tried to do some mods to it, but was unsuccessful. I am not very familiar
with VB.

The application that I am using this in is a budget which contains a
worksheet for every month. Each sheet is labeled in the following format:
"Jan, Feb, Mar, etc.". With this labelling scheme, I use
TEXT(MONTH(NOW()),"mmm") in my formulas to access the current sheet for my
summary sheet (labeled "Summary"). It is for this summary sheet that I
want
the compiled list of names and line numbers for the current month. (All
months use the same sheet format.)

Is it possible to write the macro in such a way that when I access the
sheet
"Summary", it automatically runs the macro, updating the summary list?
(Rather than having to click a button or go to ToolsMacro)

Also, is it possible to format the list so that it displays in more than
one
column depending on the number of entries? So if I have thirty or fewer
entries, it would just fill one column, but if it gets to be more than
thirty
it would form a second column like this:

6 Entry 1 46 Entry 31
7 Entry 2 51 Entry 32
...
44 Entry 29 88
45 Entry 30 91

This would be a nice-to-have, but not an absolute necessity.

Thanks again for all your help. I really appreciate it!

"Otto Moehrbach" wrote:

Richard
This little macro does what you want. Note that this macro works on
Column B only. You had said that you didn't want the categories in
Column A
picked up at all.
This macro loops through all the cells in Column B from B1 to the last
entry
in the column. All blank cells are ignored.
For each occupied cell in Column B, this macro will put the row
number
in Column A of a sheet named "List", and the contents of the cell in
Column
B of the "List" sheet. This macro should be placed in a standard module.
Please post back if you need more or you want to make some changes. HTH
Otto
Sub ListData()
Dim RngColB As Range
Dim i As Range
Dim Dest As Range
Set Dest = Sheets("List").Range("A1")
Set RngColB = Range("B1", Range("B" & Rows.Count).End(xlUp))
For Each i In RngColB
If IsEmpty(i) Then GoTo NextCell
Dest.Value = i.Row
Dest.Offset(, 1).Value = i.Value
Set Dest = Dest.Offset(1)
NextCell:
Next i
End Sub
"Richard Walker" wrote in
message
...
I have ten sets of cells of ten cells each (each cell is on a different
line). Each cell may or may not contain data. I want to build a
summary
sheet listing only data within the cells and the line number that that
cell
is on. (omit all blank cells) I hope this makes sense.

The application is a budget worksheet that has ten categories with ten
line
items in each category. Each category may contain blank lines. I want
a
concise summary on a separate sheet, eliminating the category headings
and
all blank lines. How do I do this?

Thanks in advance.