Keeping data without losing Formula's

[Inneed]

I have a worksheet (named monthly) with a column of data under a certain month (which will change each time data is entered - stocktake). However this column needs to be edited each month and has formula's in it so it will work (not always the same data in the column).

I decided to put this data into a yearly sheet under the particular month. The only way I can do it at the moment is to copy and paste special. How do I (or is there a way to) put this data in a sheet that has all the months but under the month that is signified.

In other words I don't want formula's in the yearly sheet (can't use vlookup) as I want to keep this sheet with all the data in it!

I'm sure it's really simple but I have read the VBA help files and they don't help me.

[Bryan Hessey]

It's always a problem to write a 'push' option into a system that is basically a 'Pull' system, ie, the formula is normally in the receiving cell.

Does you reference to VBA mean that you could add a button, and use a cell on your current worksheet to indicate the columns in the Annual sheet into which to put the data, something like if you put F (for column F on Sheet2) into cell A1 and

cn = Range("A1").value
Range("Sheet2!" & cn & "10") = Range("a10").Value
Range("Sheet2!" & cn & "11") = Range("b10").Value

or a row number someting like: A1 contains 20

rw = Range("A1").value
Range("Sheet2!a" & rw).Value = Range("A10").Value
Range("Sheet2!b" & rw).Value = Range("B10").Value

to copy A10 and B10 to Sheet2 cell F10 and F11 as a column thing, or
to copy A10 and B10 to Sheet2 cell A20 and B20 as a row thing.

Your totals would then copy to Sheet2 (your annual sheet etc) whenever you pressed the button, and would use the value from A1 (or other designated cell)

Is this in the right direction?

I have a worksheet (named monthly) with a column of data under a certain month (which will change each time data is entered - stocktake). However this column needs to be edited each month and has formula's in it so it will work (not always the same data in the column).

I decided to put this data into a yearly sheet under the particular month. The only way I can do it at the moment is to copy and paste special. How do I (or is there a way to) put this data in a sheet that has all the months but under the month that is signified.

In other words I don't want formula's in the yearly sheet (can't use vlookup) as I want to keep this sheet with all the data in it!

I'm sure it's really simple but I have read the VBA help files and they don't help me.

[Inneed]

Thanks Brian,

I can make it a "pull" system but I want to keep the monthly column of data under that particular month in the annual sheet. Because I only have the 1 column that changes in the other sheet.

The only way I can think of is to copy the whole sheet and pastespecial "values" but then I would lose all the "pull" formula's. I just need a way to "lock" the column in place (probably by pressing a button, however I want to stay away from copying and pasting as I will lose formula's and I need the spreadsheet next year).

Thanks again.

[Bryan Hessey]

I take it from your reply that you are not familiar with VBA code, which can make it 'push'

Try this simple test:

In a test workbook, put the letter G in cell A1 and a monthly figure in cells A10 and B10

In your Excel, click on View, Toolbars and tick Control Toolbox

The first icon is 'Edit Mode' (when you click, it becomes 'Exit Edit Mode')

In Edit mode, click on the Button icon on the same bar, then click into a cell where you want the button to be (it is moveable later)

Rightmouse on this button, and select View Code, and you will be taken to the code in the VB editor (where your macros are stored etc)

put the code

cn = Range("A1").value
Range("Sheet2!" & cn & "10") = Range("a10").Value
Range("Sheet2!" & cn & "11") = Range("b10").Value

into that, and close and Exit Edit Mode

in A1 put the column name that you want to save to, then click the button.

Your figures are moved.

Change the A1 letter to any column and press button again, figures are copied.

That should provide what you asked for, and you can set the letter (in A1 or elsewhere, it's collected in the first line of code) by any means you wish, ie A1 can be the result of a formula.
A10 and B10 are just two test cells, you will need to adjust the names to suit your own needs, but let me know if you have troubles.

in Edit Mode, the Properties of the button include BackColour and Caption which can be set to your preferences.

Thanks Brian,

I can make it a "pull" system but I want to keep the monthly column of data under that particular month in the annual sheet. Because I only have the 1 column that changes in the other sheet.

The only way I can think of is to copy the whole sheet and pastespecial "values" but then I would lose all the "pull" formula's. I just need a way to "lock" the column in place (probably by pressing a button, however I want to stay away from copying and pasting as I will lose formula's and I need the spreadsheet next year).

Thanks again.