Find last column & copy/paste values
 

Hi all

I'm working on a table of data where we update each months data AND hold
last months data.

Example Table:
Jan_10 Feb_10 Mar_10 Apr_10 May_10 etc...
Prod 1 55 10 0 0 0
0
Prod 2 5 0 0 0 0
0
Prod 3 0 10 0 0 0
0
Prod 4 55 10 0 0 0
0
etc..

All figures for forward months (e.g. from Feb onwards) are hlookups or
vlookups formulas that source data from other worksheets within this
workbook. e.g. enter Feb_10 into M1 and the lookups work for Feb_10 column.
(Feb_10 is entered as text).

But, when we change the lookup in M1 to Feb_10 - we loose Jan_10 data from
the table (goes back to 0's).

I currently have to manually copy/paste the old months data (in order to
keep it).

How do I write a macro that will :
- lookup the range and select the last column of formulas that show a result
- then copy/paste values that column.

Range for data table is M4:AM80 - each column is a month (rolling years)
with the last column totalling them.

Note: Sometimes some formula results will be 0 (zero), but not in all cells
in the column. e.g. use If "total value of the column" 0 then ...

I would really appreciate any help with this.
--
Thank for your help
BeSmart

Find last column & copy/paste values
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BeSmart" wrote in message
...
Hi all

I'm working on a table of data where we update each months data AND hold
last months data.

Example Table:
Jan_10 Feb_10 Mar_10 Apr_10 May_10 etc...
Prod 1 55 10 0 0 0
0
Prod 2 5 0 0 0 0
0
Prod 3 0 10 0 0 0
0
Prod 4 55 10 0 0 0
0
etc..

All figures for forward months (e.g. from Feb onwards) are hlookups or
vlookups formulas that source data from other worksheets within this
workbook. e.g. enter Feb_10 into M1 and the lookups work for Feb_10
column.
(Feb_10 is entered as text).

But, when we change the lookup in M1 to Feb_10 - we loose Jan_10 data from
the table (goes back to 0's).

I currently have to manually copy/paste the old months data (in order to
keep it).

How do I write a macro that will :
- lookup the range and select the last column of formulas that show a
result
- then copy/paste values that column.

Range for data table is M4:AM80 - each column is a month (rolling years)
with the last column totalling them.

Note: Sometimes some formula results will be 0 (zero), but not in all
cells
in the column. e.g. use If "total value of the column" 0 then ...

I would really appreciate any help with this.
--
Thank for your help
BeSmart