I put the first transaction on the row after the category on sheet2. try
this with no data on sheet2 except for the rows with the categories. I csan
make changes later if necessary. I don't know where you have the "Forecast
Amount" so I can't calculate a balance. Let me know where you have the
forecast so I can make changes.

I working all night in NJ so I will be up to respond to your futher requests.


Sub move_data()

With Sheets("Sheet1")
RowCount = 2
Do While .Range("A" & RowCount) < ""
Trans_Date = .Range("A" & RowCount)
Trans = .Range("B" & RowCount)
Category = .Range("C" & RowCount)
Amount = .Range("D" & RowCount)
With Sheets("Sheet2")
Set c = .Columns("B:B").Find(what:=Category, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find categroy = " & Category)
Exit Sub
Else
'look if there is data in column c
If c.Offset(1, 1) = "" Then
Data_Row = c.Row + 1
Else
Data_Row = c.Offset(1, 1).End(xlDown).Row
Data_Row = Data_Row + 1

End If
.Rows(Data_Row).Insert

.Range("C" & Data_Row) = Trans_Date
.Range("D" & Data_Row) = Trans
.Range("E" & Data_Row) = Amount
End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub


"Burton" wrote:

ok I copied the data to the form that you suggested. Column A contains the
word "Category" Colum B has the category in the column. Each Category is
seproated by a space with a repeating word category and the new catogory name
in column B. Column C is the date, Column D is the payee Column E is the
amount. The rest of the formating is just basic spreadsheet programming. All
categories are on the top of each other. Does this help with writing the
macro? I'm lost when it comes to programming excel. Thanks in advance

Burton


"Joel" wrote:

This is not too difficult. The problemn is how sheet three is setup by
categoies.. I would have sheet 3 have the word category in Column A tand the
category name in Column B. Also have one blank row betweenthe end of one
category and the start of the next category. It would also make the macro
simplier if the data started in Column C. The macro would work something
like this

1) Search column B for the category
2) Then go to column C and move down to the end of the category using :
"end(xldown)"
3) Then insert a new row after the end of the category and put the data into
this row.

I didn't write the macro because I'm not sure which columns you data is
located.
"Burton" wrote:

I am making a workbook to track my spending

this work book is comprised of four sheets
"sheet1" is used to enter raw data as money is spent
"sheet2" is a category sheet used to populate a drop down menu in the
category column in "sheet1"
sheet three is a sheet that is dynamically updated by caegory when data is
typed into sheet 1
"sheet4" is a monthly budget sheet


for example on sheet one I have the following columns. Lets say that I have
purchased a bottle of oil from Ricks Auto. I would enter the data in sheet 1.


Ballance as of 1/1/2008
$300.00
Date Transaction Category Amount Spent
Balance
1/23/2008 Ricks Auto Auto $1.50
$298.50



After I enter this transaction I press a button and the computer looks at
this row and sorts this transaction out as a transaction in the "Auto"
category it copies the data and places it in a preformated table on sheet
three. Sheet three is set up something like this

Categoty: auto

Forcasted Amount To Spend $120
Date Transaction Amount Spent Ballance
1/23/2008 Ricks Auto $1.50 $118.50

The Forcasted Amount is brought in from "sheet4"

These tables are set up for each category and will either be set up only
when a button is pressed.

I am rather new to programming excel and would like some pointers in the
right direction. I've taken a look at pivot tables, but can't seem to get
them to form in the fromat that I want. Is there any one out there that can
point me in the right direction to making this sheet work. Thnakyou in
advance.

Burton