Macro only runs on sheet i recorded it on.

just to add to this, all the tabs are excat copies of each other, other than having a different date. Im trying to use this macro to jsut simply be able to do a paste and it sort, and format the page.

Hello and welcome to the forum - unfortunately before we go any further you need to read the forum rules and use code tags around any code that you post. If you edit your original post then I'm sure somebody will be happy to help you.

Originally Posted by Andrew-R

Hello and welcome to the forum - unfortunately before we go any further you need to read the forum rules and use code tags around any code that you post. If you edit your original post then I'm sure somebody will be happy to help you.

Ok, i think i wraped it correctly.

Yup, looks like the job.

Macros written with the macro recorder are rather inefficient, because they have in a lot of selecting data which doesn't need to happen. It's late here in the UK, so rather than rewrite this properly I've just hacked it to work, but work it should...

Please Login or Register  to view this content.

Originally Posted by Andrew-R

Yup, looks like the job.

Macros written with the macro recorder are rather inefficient, because they have in a lot of selecting data which doesn't need to happen. It's late here in the UK, so rather than rewrite this properly I've just hacked it to work, but work it should...

Please Login or Register  to view this content.

I replaced my existing code on in th emacro with this.

Now when i try to sue ctrl i to do the special paste it does not paste anything.

Please don't quote entire posts when replying, it just clutters up the thread.

Is the Ctrl-i short-cut still assigned to the macro? That would be the first thing to check.

Originally Posted by Andrew-R

Please don't quote entire posts when replying, it just clutters up the thread.

Is the Ctrl-i short-cut still assigned to the macro? That would be the first thing to check.

Sorry about that.

Ok, that was the problem. Now im having an issue when i run the macro.

run-time error '1004':

PasteSpecial method of Range class failed.

Do you have any idea what could cause this?
Thanks for all your help.

It looks like the issue is that you're trying to paste special without actually copying anything first - are you running this macro after selecting a range to copy?

Originally Posted by Andrew-R

It looks like the issue is that you're trying to paste special without actually copying anything first - are you running this macro after selecting a range to copy?

yes, im copying the datafrom a seperate spreadsheet. and trying to paste it into the one the macro is ran on.

OK, that's what's causing the problem. On the first pass through the worksheet loop you're pasting the data then turning the cut and paste mode off, so subsequent passes through have nothing to paste.

Is it the range "Table2" that you're pasting?