Macro Required to added week numbers to columns

Hi,

Every week I get sent a spreadsheet with the hours booked against specific codes, however, each weeks data is in a separate column (from weeks 1-52).

What I require is a macro that will check the heading title of each weeks data e.g. WK01, WK02 etc, work out what week it is and input the week number in a new column (Week No) corresponding to that weeks data and also total the time for that week and enter in the Total Hours column

Please see attached a sample spreadsheet for clarity.

Please also note that I have asked this question before (http://www.excelforum.com/excel-gene...o-columns.html) and DonkeyOte kindly supplied me with the code to work out the week number i.e.

=IF(COUNT($D2:$BC2),SUBSTITUTE(LOOKUP(9.99999999999999E+307,$D2:$BC2,$D$1:$BC$1),"Hrs WK",""),0)+0

however, as the weeks progress I have to manually copy the week number down and add the totals up so I believe a macro would be the best option as I am currently repeating the same task every week.

The macro needs to:-

(a) work out the week number and enter in ‘Week No’ column
(b) macro to copy each weeks data into Total Hours column

I would be most grateful for any assistance in this matter

Many thanks in advance


Regards


Rob

N.B. Excel version 2003

still dont think you require macro
in g2
=MATCH(99^99,I2:BH2) dragged down
and in h2
=SUM(I2:BH2)
dragged down

Martin

sorry I can't seem to get =MATCH(99^99,I2:BH2) to work !

I'm I doing something wrong ?


Rob

Martin,

thanks for getting back to me it is very much appreciated. That said, I reformatted column 'G' and it does now report the last week with data in i.e. I:BH.

However, what I was after is for the code/macro to look in each column i.e. I:BH to check which columns have data in (>0) and then using the weeks column heading e.g. Week 2 insert the week number in the associated cell in column 'G' (WK No)

Any assistance would be greatly accepted

Regards

Rob

well it works on the principle that nothing is entered in in a column until you need to, why put 00.00 in? just leave blank then the match function finds the last column with a number in it(which would = week number) unless someone hasnt hours that week then put in 00.00 for them

Martin,

thanks again, based on what you said...I went to Tools, Options and on the View tab unchecked 'Zero Values'....and guess what ???


It worked a treat !!

Many many thanks you have saved me hours of boring repetitive copying and pasting over the next coming year.

If ever your in Cardiff I’ll buy you a pint or six !!


Best Regards


Rob

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save