Excel : Clickable buttons to copy a value to "current month" field?

Lil306

Just busy making myself a little savings spreadsheet which shows budgets for a house, shopping prices, wages with paye tax deductions etc.

On the shopping list sheet I'm trying to design it so that I fill in the shopping list for say "january" and then the total is displayed, I can then click a button and that value is then copied over to the relevant specified cell for the january column

How is this possible, VB, or simpler functions with some form of "DATE/NOW" functions?

I want to do it so I can do month by month reporting and also then set it up so eventually I can do it on a year by year basis, nice little project for myself

Quiet_Life

click on the
relevant specified cell for the january column
go to the auto sum symbol on the tool bar and click auto sum
fo to the worksheet cell that will have the jan total figure and click on that
go to the green tick beside the where the formila is displayed and tick that
I hope you can understand that... haven't explained it very well

Lil306

Thanks,

I understand what you're saying I'm not sure if that's what I'm looking for. I was hoping to automate it possibly with a VB script or a clickable VB / Form Button

I'll try and describe it better (gimme 5 mins)

e.g

Say cell B1 is used for the total of all the shopping
Say cell C1 is used for january 2010 monthly expenses
Say cell D1 is used for feb 2010 monthly expenses

I want to click a Form/VB button, that will copy all data from B1 into the relevant month for that year. Like, if it's feb 2010, that will then automatically copy the data to D1

I'm trying to automate it as opposed to doing it manually, cos I like dabbling with excel and it's also linked up to multiple cells via absolute references, i.e $c$1 is linked to monthly housing expenses and is used in a calculation

It would still work, but I don't wanna do things manually, I like the lazy automated approach keep my life simple

somethingcorporate

Lil306 wrote: »

Thanks,

I understand what you're saying I'm not sure if that's what I'm looking for. I was hoping to automate it possibly with a VB script or a clickable VB / Form Button

I'll try and describe it better (gimme 5 mins)

e.g

Say cell B1 is used for the total of all the shopping
Say cell C1 is used for january 2010 monthly expenses
Say cell D1 is used for feb 2010 monthly expenses

I want to click a Form/VB button, that will copy all data from B1 into the relevant month for that year. Like, if it's feb 2010, that will then automatically copy the data to D1

I'm trying to automate it as opposed to doing it manually, cos I like dabbling with excel and it's also linked up to multiple cells via absolute references, i.e $c$1 is linked to monthly housing expenses and is used in a calculation

It would still work, but I don't wanna do things manually, I like the lazy automated approach keep my life simple

It is certainly possible using VB but would take longer to write the code than to simply copy and paste special > values at the end of each month!

somethingcorporate

Lil306 wrote: »

Thanks,

I understand what you're saying I'm not sure if that's what I'm looking for. I was hoping to automate it possibly with a VB script or a clickable VB / Form Button

I'll try and describe it better (gimme 5 mins)

e.g

Say cell B1 is used for the total of all the shopping
Say cell C1 is used for january 2010 monthly expenses
Say cell D1 is used for feb 2010 monthly expenses

I want to click a Form/VB button, that will copy all data from B1 into the relevant month for that year. Like, if it's feb 2010, that will then automatically copy the data to D1

I'm trying to automate it as opposed to doing it manually, cos I like dabbling with excel and it's also linked up to multiple cells via absolute references, i.e $c$1 is linked to monthly housing expenses and is used in a calculation

It would still work, but I don't wanna do things manually, I like the lazy automated approach keep my life simple

It is certainly possible using VB but would take longer to write the code than to simply copy and paste special > values at the end of each month!

Lil306

Hahah np, for now I'll just copy and paste. I'll research how to do it myself and I'll bump the thread if I ever figure it out incase this is searched

ScratchandSniff-->[X]

Can't understand your description I'm afraid!

Mike_by_the_Sea

Lil306 wrote: »

Just busy making myself a little savings spreadsheet which shows budgets for a house, shopping prices, wages with paye tax deductions etc.

On the shopping list sheet I'm trying to design it so that I fill in the shopping list for say "january" and then the total is displayed, I can then click a button and that value is then copied over to the relevant specified cell for the january column

How is this possible, VB, or simpler functions with some form of "DATE/NOW" functions?

I want to do it so I can do month by month reporting and also then set it up so eventually I can do it on a year by year basis, nice little project for myself

Hi Lil306

If you have a column that has the date or month in it, you can set up a SUMIF function that will give you sub-totals for that specific month etc. If you would like an example, just drop me a pm with an e-mail addy and I will send over a spreadsheet.

Good luck

Mike

Vomityspice

If you know the logical process you want to follow, why not just use the macro recorder. This will record your action and then assign your recorded macro to a user form button. No programming skills required

Lil306

ScratchandSniff-->[X] wrote: »

Can't understand your description I'm afraid!

One of the worksheets will have a shopping list on it, I will have a total in say cell D1 and a button which I can click on to copy the value from cell D1 and maybe clear the list for the current month. So the system would make use of a lookup on the DATE argument or something similar. It would see the month was july and when you click the button it copies it to the july entry.

Mike_by_the_Sea wrote: »

Hi Lil306

If you have a column that has the date or month in it, you can set up a SUMIF function that will give you sub-totals for that specific month etc. If you would like an example, just drop me a pm with an e-mail addy and I will send over a spreadsheet.

Good luck

Mike

Not sure that will work for the way I want it, I'm not going to keep the shopping list. mainly choose what I want that month/trip and once it's done delete it, but saving cost to that months cell

Vomityspice wrote: »

If you know the logical process you want to follow, why not just use the macro recorder. This will record your action and then assign your recorded macro to a user form button. No programming skills required

A macro would only save that value to one cell each time, to my knowledge you can't record a macro that looks up a date and then puts that value in the appropriate column




Thanks for all the help. For the moment I'm going to copy it manually

PhilCo_2

Try something like the following:

total = Sheets("sheet2").Range("d1")
datex = Sheets("sheet2").Range("a2")
monthx = Month(datex)

Sheets("sheet1").Activate
Range("b1").Select
ActiveCell.Offset(monthx, 0).Select
ActiveCell.Value = total

where Sheet1 is your monthly totals and Sheet2 your list. Assuming you have the date per line item, the code will use the date in A2 and get the month from it. Sheet1 has months in A2 : A13 with totals to be pasted in column B. The code will offset down the list the amount of months in the date and then insert the total.