Thread
:
Find last column & copy/paste values
View Single Post
#
2
Posted to microsoft.public.excel.programming
Don Guillett[_2_]
external usenet poster
Posts: 1,522
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
Reply With Quote
Don Guillett[_2_]
View Public Profile
Find all posts by Don Guillett[_2_]