VBA Copy and paste when cell changes

[outlook888]

I have a spreadsheet which automatically upgrades itself periodically by pulling data from a website.

Whenever cell A1 data changes, I need to have A2:A10 copied and paste to the last available column. So end of the day I will have about 1000 columns of end of minute data in my spreadsheet in rows 2:10.

Example.
Live data A1 updating periodically
When there are any change in a cell A1, the VBA will copy cells A2:A10 and paste it in B2:B10 (which is the last available column)
At the next update in cell A1, the VBA will copy A2:A10 and paste it in C2:C10 (which is the next available empty column.

I manage to do the cut and paste with the following.

Please Login or Register  to view this content.

can someone help me include the worksheet_change for cell A1 so that the whole macro runs when A1 changes. I am confused with including PRIVATE SUB worksheet_change

What changes must I do to the VBA such that the updated cells are pasted on the right most available column. The above VBA always paste updated column on column B after moving old columns to the right.

Moderator Note:

Pls use code tags around your code next time as per forum rules.

[ragulduy]

Please Login or Register  to view this content.

[outlook888]

Hi, When I try to run this VBA it ask me to name the macro which then tell me invalid procedure name.

What must i add to run this macro in a sheet?

[ragulduy]

Hi, you place the macro in a worksheet object and then the macro is triggered by changing a cell on the worksheet. You cannot "run" these macros as you would a standard one.

[JOHN H. DAVIS]

FWIW:

Please Login or Register  to view this content.