Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynamicall
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynamicall
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynami
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynami
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynami
Where di I put this code on sheet 2?
"Joel" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynami
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" wrote: Where di I put this code on sheet 2? "Joel" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynami
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 "Joel" wrote: 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" wrote: Where di I put this code on sheet 2? "Joel" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynami
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. "Joel" wrote: 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" wrote: Where di I put this code on sheet 2? "Joel" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynami
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" wrote: 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" wrote: Where di I put this code on sheet 2? "Joel" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort data and copy to another sheet in a different form dynami
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 "Joel" wrote: 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" wrote: Where di I put this code on sheet 2? "Joel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking forMacro to do this Sheet to Sheet copy &sort | Excel Discussion (Misc queries) | |||
Copy specific data form sheets into master sheet | Excel Worksheet Functions | |||
copy and sort data then past in specific area on new sheet | Excel Programming | |||
Copy my active sheet to a new sheet and open with an input form | Excel Programming | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming |