Macro fails when adding a sheet.

Question

Hello,

I have written a macro where I open an excel worksheet and rearrange the data then prompts you to open another worksheet and using the

Sheets.Add After:=ActiveSheet

Then paste the rearranged data from the first worksheet to the added sheet.

I am using MicroSoft Office 365 and the macro works great. I have sent a copy to are office in Spain that is using Excel 2016 and they are getting a "Run-time error '1004' Method 'Sheets' of object'_Global' failed" error.

I am not sure what is wrong.

Here is the section of code that is failing

Does anyone have any suggestions?

Thank you in advance.

Rich

Answer 1

Hello Peiyezhu,

It turns out it was priority and protected settings on their side that was causing the issue.

Thanks for your help.

Rich

Answer 2

New sheet is activesheet,so may be conflict.

try below:

set sht=activesheet

sheets.add after:=sht