Send data from input to relevant worksheet using command button?

**Si902** · 03-02-2011, 10:37 AM

Hi all,

Thanks in advance for taking time from your busy schedules to look at this thread.

I am developing a workbook, a copy of which is attached for your info.
I need to populate data once per day over the period of a month, then, at the end of that month save that data (including the charts) to the relevant named worksheet for that month by clicking on a command button.

I have some code to do a similar thing on a home accounting worksheet, i have tried to alter it to fit, but, to be honest, i have no idea when it comes to VBA.

Any help / pointers in the right direction would be greatly appreciated.

Thanks.

**barryleajo** · 03-02-2011, 07:23 PM

Hi Si902
May not have interpreted your brief entirely correctly but this should get you going
barry

**Si902** · 03-03-2011, 01:34 PM

Thanks Barry

That works great for the data in the cell ranges you included, sorry for being a little unclear.

i'm most of the way there now with your help.

Is there any way of copying the charts from the input page to the relevant month using the command button?

Cheers

Si

**barryleajo** · 03-03-2011, 03:55 PM

Hi Si
Glad you are on your way with this part of your project.

Yes it is possible to copy charts but it can be troublesome in a number of ways.
For example, they tend to still have links to the data series on the donor sheet.
However, there are a couple of other options/solutions for you to consider.

ONE (my preferred option)
Spend just a bit more time setting up your monthly worksheets.
Copy or create your charts in situ on each of the sheets and reference the data series to where you will paste the data.
Then when you paste the data i.e. no change to the macro already supplied, the graphs will automatically update.
Note that in the macro you will have to reposition the paste destination of the second set of data to lower down the sheet if you want to maintain the same layout as the 'Input' sheet.

This approach has the advantage that you could write a simple macro to clear the pasted data and hey presto you would have a workbook already formatted for next year and subsequent years.

TWO
You could copy the entire worksheet and rename it as you save the data - through VBA if necessary.
You will need to write a new macro and may need to build in checking options about whether the sheet already exists etc.

Hope this helps
Good luck
barry