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

Lil306

I hope the below helps to explain it better

The way I want it to work
- Shopping total to be added up, simple SUM put into cell D15
- Click the button "save to master list", the value from D15 is put into relevant month / year cell

Ignore it being replicated thats because of the formula when I was playing around, in this instance when clicking the button I would want to put the value from D15 in cell K2 (if today was really month end)

EDIT - I know I can manually copy it

However, I'm trying to automate it if possible, because I may be adding in a lot more advanced functions at a later date, since the Excel sheet already has quite a bit of stuff in now

PhilCo_2

Try:

total = ActiveSheet.Range("d15")
monthx = Month(Date)
yearx = Year(Date)
yearmatch = WorksheetFunction.Match(yearx, Range("D:D"), 0) - 1
Range("d1").Select
ActiveCell.Offset(yearmatch, monthx).Select
ActiveCell.Value = total

It will take the month and year from todays date and find the corresponding cell and enter D15 in it.

Lil306

PhilCo wrote: »

Try:


It will take the month and year from todays date and find the corresponding cell and enter D15 in it.

Thanks for the help Phil

You can use [code] to get rid of smilies

I get an error in the script
"runtime error 1004: range of object_global failed"

When going to VB Debug, it highlights this....

yearmatch = WorksheetFunction.Match(yearx, Range("D : D"), 0) - 1

PhilCo_2

Have you taken the spaces out of the range bit? You might also have to make it D1: D12, sometimes you can get away with naming the column, other times it likes an exact range.

Lil306

PhilCo wrote: »

Have you taken the spaces out of the range bit? You might also have to make it D1: D12, sometimes you can get away with naming the column, other times it likes an exact range.

thanks ill try it now, i didn't notice there was spaces (because of smilies)

Lil306

Cheers Phil your a star

Was just the range because of the smilies without spaces, removed them and it put the value in the correct month

Thanks