Macro to move a cell right every time it runs

[mumford54]

Hi

I am new to this forum and know basic excel and i am runing Excel 2003.

I have a spreadsheet with sales figures for a company over a few months. I want to create a macro that will insert a new month after the last month. I have got it to work for the first month but after that it will just insert 2 black cells after.

i have a named cell called Average_slales_per_month that is next to the last month all the time.

Any help would be much appreciated.

i have hopefully attached the sheet to this thread

Regards

Stephen Mumford

[royUK]

Wouldn't it be easier to creaste the necessary columns first & hide them. Code could then unhide columns according to the month.

This wouldn't be a college project would it?

[mumford54]

Yes it is


And I am afraid I have to demonstrate it in a macro

[royUK]

Have you been asked to actually insert a column?

What code have you used so far?

[mumford54]

i have beren asked to

"create a macro to add a new colimn for sales datas to be inserted. This should insert relevant period details automaticallt and allow all exsisting statics to update without user input"

i have attached the file with the macro i have been using

[broro183]

hi Stephen,

Edit: Ooopps! I hadn't seen any of the posts after your first one when I posted this (if it wasn't for the fact that this is a project - Central Sussex College?, I would/still do strongly suggest redesigning the file as mentioned below or even to use a Pivot Table)...

Original text:
I've had a look at your file & I suggest moving your summary/calculated columns (ie col O to col R) to the left of the sheet eg b/n the existing columns B & C. This way the summary data is always visible at the left of the sheet (esp with the use of Freeze panes or "coolumns to repeat at left" in the printing options) - no matter how many months you keep records for.

If you are still keen on a macro with the existing format try:

Please Login or Register  to view this content.

hth
Rob

[mumford54]

Please Login or Register  to view this content.

Hi i have been loking at your code and i am very intrested how it works. could you explain step by step whet you did ..

Also i need to make it so that it will update the formula that ill be dependet on the new colum cells

[broro183]

hi Stephen,

Here's what the original code does:

Please Login or Register  to view this content.

Also i need to make it so that it will update the formula that ill be dependet on the new colum cells

What do you mean (does it relate to the ".value = .value" line)?


On rereading your first post I can see that the "Find" section isn't need & the code can then be shortened slightly to:

Please Login or Register  to view this content.

You may find the below links useful for learning more about Excel:
http://www.excelforum.com/showthread.php?t=620254

http://www.mvps.org/links.html#Excel

hth
Rob

[mumford54]

What do you mean (does it relate to the ".value = .value" line)?


i don't think this is what is need but thanks any way

[broro183]

hi Stephen,

Sorry, it seems we've confused each other. Does the current code do everything you want?

If not, let us know...

Rob