A question about VBA in Excel

[KOBE]

if I have a Excel file which contain 5 sheet
each sheet have a different name

david , may , eva , john , mary

each sheet contain some information about useage of money

record in the fellow field : Date , Item , Amount
date mean the date of money used
item mean what is the money used for
amount is the amount of money used

each sheet record each person used how much money in 5 month

now if I want to make a monthly report to show those 5 person's money useage
such as create a new sheet called "Jan" , and this sheet will contain david
, may , eva , john , mary these 5 person useage of monry in january
then frbruary , march april etc..
depend on the date in original sheet

now I want to ask , is it possible if I use VBA to do it ?
then if it is possible , which topic I need to search in VBA
I want to study it , but I don't know which kind of information that I need
to search in google

if you know , please tell me

thx







--

 

[Grochu]

Could you describe you problem a bit more accurately? what exactly u
don't know?

For your very general question general answer - yes, it is possible,
look for VBA in Excel

 

[George Nicholson]

If you can do it manually, you can automate it with VBA. So, the question
becomes, how would you do it manually?

One approach:

Apply AutoFilter to sheet("david")
Criteria: Date >= 01/01/05 and < 02/01/05
Copy results
Paste results to Sheet("January")
Repeat for may , eva , john , mary

To get started, you might try turning on the MacroRecorder and do one step
manually.
The macro recorder produces incredibly verbose and very simplistic code but
you can learn a lot from viewing and "cleaning up" the results. If nothing
else, it would give you some clues as to what you need to "research"
further: loops, dynamic ranges, etc.

HTH,

 

[KOBE]

thx for your help
I will search those command in google
and study how to use them

If you can do it manually, you can automate it with VBA. So, the question
becomes, how would you do it manually?

One approach:

Apply AutoFilter to sheet("david")
Criteria: Date >= 01/01/05 and < 02/01/05
Copy results
Paste results to Sheet("January")
Repeat for may , eva , john , mary

To get started, you might try turning on the MacroRecorder and do one step
manually.
The macro recorder produces incredibly verbose and very simplistic code but
you can learn a lot from viewing and "cleaning up" the results. If nothing
else, it would give you some clues as to what you need to "research"
further: loops, dynamic ranges, etc.

HTH,

 

[KOBE]

David sheet
date item amount
1/2/2004 paper 50
30/3/2004 pencil 80

Mary sheet
date item amount
1/3/2004 paper 50
1/4/2004 pencil 80

Chris
date item amount
1/3/2004 paper 50
29/3/2004 pencil 80

then I can automatically generate 3 new sheet
February , March , April (depend on the data in the name sheet contain which
month)

For example in March Sheet :

david
30/3/2004 pencil 80
mary
1/3/2004 paper 50

Chris
1/3/2004 paper 50
29/3/2004 pencil 80

this I want to do in excel

 

[KOBE]

so I want to know if VBA can do this
I need to look for which topic of VBA

as I go google to search , there are too many information

 

[KOBE]

actually , I want to ask what is VBA ?
is it one type of vusual basic ?
or it is one of the excel function , but similaer to visual basic ?
so it call "visual basic application" ?

If you can do it manually, you can automate it with VBA. So, the question
becomes, how would you do it manually?

One approach:

Apply AutoFilter to sheet("david")
Criteria: Date >= 01/01/05 and < 02/01/05
Copy results
Paste results to Sheet("January")
Repeat for may , eva , john , mary

To get started, you might try turning on the MacroRecorder and do one step
manually.
The macro recorder produces incredibly verbose and very simplistic code but
you can learn a lot from viewing and "cleaning up" the results. If nothing
else, it would give you some clues as to what you need to "research"
further: loops, dynamic ranges, etc.

HTH,

 

[Grochu]

What you need to implement is kind of schema below (only my proposal):

1. Check how many sheets with data you have
Useful VBA:
objects: sheets, range, cells
methods: count
if you have constant amount of people to report (this case only david,
mary and chris) u do not neet to check

2. for every sheet with data u need to check every row of data and
according to date move this record to sheeet of a proper month
useful VBA:
objects: shets, range, cells, row, column
methods: select

to check all data from one sheet and stop in thefirst empty row (ie
"david" sheet) use:
sheets("david").range("A2").select
do until activecell = ""
sheets("david").range("A" & activecell.row + 1 & ").select
loop

to check month get data to variable using function "month"
month will return number of month - then u will know wich sheet move
your data to

so first learn about
* basic Excel objects: sheets, range, cells
* variable types
* some time function: month, year
* methods for objectss above, especially: select
and constructions:
* if
* for ..... Next
* do until/whille.... loop

that's still general, but shows u some direction
everything depends how complicated and dynamic your raport should be


VBA is VB implemented to use objects of applications like Excel,
Access, Word, etc - it is just VB but poorer then u can find in Visual
Studio for example
everything you can do manually - you can do using VBA
and dont use google to learn basics of VBA - use VBA help in VBA Editor
good luck

 

[KOBE]

great~
it really help me a lot
thx