Macro to sum the cells left of the current week number

[hoss88]

Hi

I would like a macro to insert a column to the left of a current week number and sum its values to the left from column E. For example the macro would select the week number based on current date (currently week20) and then insert a column to the left and sum its values from column E to the inserted column. Please see the attached spread sheet for more details.

Any help would be greatly appreciated.

Many Thanks
Hoss

[ragulduy]

maybe something like:

Please Login or Register  to view this content.

[hoss88]

Dear ragulduy

Thank you for your reply. When I run your macro the value of arrears is 53 and should be 62, i.e. it seems to only add up to week17, column U. Also sorry I should have pointed this out but my spreadsheet has about 100 rows of data therefore how can the results be autofilled to cover all the rows of data.

Thanks
Hoss

[AB33]

I do not know why week-19 is 0 in your output, while it shows 9 in the input data.
I also do not know where to insert a column. I have tested the code on 3 different days and it works. The output data is in A10. This can easily be changed once I know where to put the data in.

[hoss88]

Dear AB33

you for your reply and sorry for taking this long to respond to your reply.

Your macro works if quantiy for the selected week number is 0. For example if week number is 19 the output is 62 but it should return 53 (it includes qty 9 for week 19 where as it should add up from week 1 upto 18).

The second request is that the macro does not work from week 1 to 9, could you please extend so that I can select output from week1. (week1 will return 0 arrears - week2, 1 arrears - week3, 9 arrears and so on).

Lastly as my data covers about 100 rows is it possible to display output for all the rows as oppose to only row2?

I tried to modify your code but did not understand it to make the above changes.

Thanks
Hoss

[AB33]

Hoss,
I have amended the code. The only bit left is the number of rows to copy.
I do not know how comfortable you are to adjust this line of code.

Please Login or Register  to view this content.

Basically this line Rng.Row + 1
copies row 2, so if you want to copy 100 rows, change this line. If it is too much hassle, I need to change the code.

[hoss88]

Dear AB33

Thank you so much - it's exactly what I was looking for.

Thanks again
Hoss