Macro to move a colomn to th end of the table

[Tradesman]

Hi guys,

I put this probelm within another thread.

I have table that involves 52 weeks of the year - the first week is the week we are in.

what is the easiest way to move that colomn to the end of the table - including all contact including formaula and conditional formatting?

recordign a macro doesnt work for metesting sheet week move.xlsx

the formula can be abosulte references so wouldnt need to be rewritten during the move

[patel45]

you attached a dummy workbook

[oeldere]

What are you getting to achieve?

Maybe it's better to make a table of your data.

After that you can use pivot table.

[Tradesman]

testing sheet week move.xlsx

what I would like to do is to run a macro to move this week numbers colomn to the end of the table - eg the far right hand side- i sue 52 weeks of the year and this is for forecast information.

so once this week is over and I enter a new week eg week 2 I want to move week 1s details and formula to the end of the table!

[oeldere]

Isn't it earier to add in a column for the new information?

[Tradesman]

No because the conditional formaulaes refer to the week no. at the top of the colomn to compare with some other data tables

eg the formuale

=+SUMIFS('PO Book'!$G:$G,'PO Book'!$D:$D,$A162,'PO Book'!$K:$K,U$5)

[oeldere]

Probably (i suggest) it's better you add an excel file.

[Tradesman]

Hi- sorry first time Ive uplaoded a file- see at 7:11 uploaded a file

[oeldere]

Oke, maybe i asked it not clear enough.

Shows us a Exel-file with formula and more data, without confidnetional information.

[Tradesman]

product follower complicated! v1.xlsx

ok I want to advance the colomns on the sheet range by 1 week and add to the end ( notice it doesnt go up to week 52 cos the updating is a complete pain)

[oeldere]

Ok, i see your file, in what sheet you're want a column to be moved?

cust demand forcast?

In that case you will put you're data behind week 43 of 2013 (or week 52 of 2013).

I don't think thats logical, but maybe that's why i don't see what your want to achieve.

[Tradesman]

theres a sheet called range- it should have 52 weeks in it but I havent updated it as it is real pain to amend all the formula- currently im deleting this weks and copying across the last data and formuales ( cos of th function )

what I do is (on this sheet-) delete week 46 - thats this week and then copy the colomn 48 across to 47 which is this week- this means I loose a colomn every week so I have to add a load of colomns every 3 months to the sheet-

one of the things I want to do is to keep rotating round the weeks so i dont have to do this- it might need resetting the tab structure?

this sheet has grown and so may need a rethimk eg have my demand-my customer demand - then add them by looking up to the range sheet - then perhaps the formula comparing the onfo could look at the week numbers - moving the week may then not mean the formual has to be moevd across-but the way I move the colomn to the end has got to eb as tarting point?? any ideas?

[oeldere]

Maybe i can't help you, but you could start to give the information we need.

If you upload a file with huge information, but not the sheet we need, you definily won't get the wright answer.

We are (I am) not interested in all the data (just a couple of rows).

We (I am) want to see what the book is built up and what the format of the cells are.

[Tradesman]

Ok sorry I am new here - Ill rejig and upload !

[Tradesman]

product follower complicated! v2.xlsx

Ok Ive streamline dthe data to one product

I would like to figure out how to add this week to the end of the table ( as as I move from 1 week 46 to week 47 ) add week 46 to the end

and be able to do that every week

on another note - how do I map the cust data onto the correct week ( using week no.s I think its If match function )

[Tradesman]

ok - Ive suses how to map the custoemr demand against the week number using this formula

=INDEX('cust demand forecast'!$A$1:$BB$140,MATCH($D$6,'cust demand forecast'!$A:$A,0),MATCH(I5,'cust demand forecast'!1:1,0))

what would be the best way to populate a load of row-s of I copy and paste the formula needs redoing eg eg cell inthe row would need to refer to I5,then J5 etc - tedious too say the least?? any ideas

[Tradesman]

product follower complicated! v2.xlsxIve now added two rows that complete the formula - however when I delete this weeks colomn-the sum falls over as it refers to a colomn thats not there

Im getting the idea that I should just be mapping today - over a virtual weekly calendar- and that the week no. just mobves my view over it-as such

so the only realy constants I have are;
1) todays week no.
2) todays stock figure
3) confirmed future demand eg orders

the rest is just virtual

and so it may be easier for me to make my view of the data as a peep view of what is happening

this would amke the formula a lot easier- ami talking sense?

[Tradesman]

Hi Any ideas?

[Tradesman]

product follower complicated! v3.xlsx

Ok so in case anyone is following this thread Ive rethought out the actual process- instead of moving the colomns I use some logic and now only chnage the week number and then basic add 1 to the number- refining it by not allowing more than 52 as a week number!