sort data and copy to another sheet in a different form dynamicall

[Burton]

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

 

[Joel]

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]

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]

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]

Where di I put this code on sheet 2?

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

 

[Joel]

In VBA you have to insert a module and put the code in a module page. From
the VBA menu go to insert menu and add module.

 

[Burton]

I think I got it working it looks good I will play with the tallied
forcasted ammount and see if I can get it working. Thanks for all of your
help you really saved me.

Burton

 

[Burton]

ok, well it worked, but when I ran the macro a second time it created
duplicates is there a simple way for the macro to sort the data and if ALL
fields match in that sorted category then it won't print it. If the entery is
a new entery then it will print on the next avalible row? Once again I
really appreciate all the help that you have given me with this page.

 

[Burton]

I hate to be a pest, but you asked where the forcasted amount will be and I
see that you need to bring this in with VBA also. Sheet 4 hasn't been built
yet, but I will copy the forcasted amount from "sheet4" to the same row as
the category in cloumn F. As a entery is made I would like the amount spent
to subtract from the aloted amount at the bottom of the tallied amount I
would like to know how much there is left before I go over budget in that
area. I know that I am asking a lot from you, but you are a big help. Once
again thanks for all you have done

 

[Joel]

I made the balance a formula in column F. also made some changes where rows
were added on sheet2. See code below.


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
If c.Offset(2, 1) = "" Then
Data_Row = c.Row + 2
Else
Data_Row = c.Offset(1, 1).End(xlDown).Row
Data_Row = Data_Row + 1
End If
End If
.Rows(Data_Row).Insert

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