run time error 1004

[lburch]

Hi,
i get this error message when i run the macro below. The macro will run for about 20 times but then this message will come up

Run-time error '1004';
To prevent possible losss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of you worksheet.
If you do not have data in cells that can be shifted off of the worksheet, you can reset which cells Excel considers nonblank. To do this, press CTRL + End to locate the last nonblank cell

Here is the macro

Please Login or Register  to view this content.

And the yellow text is what was highlighted when i went to 'DEBUG'

Any help would be greatly appreciated from someone more knowledgable..
Many thanks
lburch

[macropod]

Hi lburch,

It's little wonder you're encountering this error. Each time your macro runs, it tries to shift the existing data on BA 11 columns to the right. Eventually, it's going to run out of columns the data can be moved to.

FWIW, your code could be made much more efficient:

Please Login or Register  to view this content.

It'll still run out of columns, though, so you need to address that as the underlying issue.

[lburch]

Hi paul,
no the macro will only ever run 100 times before another macro inserts 69 more rows so i wont run out of room to the right.....and the code is just what it looks like after manually recording. i dont know enough VBA to write code myself...
i hope you have another view to what it is.....
is there a chance that 'office' is saying there s that much data that you have to save before you can insrt more cells...and if that is it how can i put a 'save file' command into a macro.
cheers
lburch

[lburch]

Hi paul,
no the macro will only ever run 100 times before another macro inserts 69 more rows so i wont run out of room to the right.....and the code is just what it looks like after manually recording. i dont know enough VBA to write code myself...
i hope you have another view to what it is.....
is there a chance that 'office' is saying there s that much data that you have to save before you can insrt more cells...and if that is it how can i put a 'save file' command into a macro.
cheers
lburch