VBA won't stop running

**codsmith** · 05-02-2011, 12:48 PM

The VBA will run and spit out what I need, but then it keeps running and creates a second copy (that I don’t need) and hits an error on renaming the second sheet. I only need the first output sheet that is renamed. I’m not sure why it’s not stopping after it produces the 1st output sheet. If I take out the part on renaming the sheet I just continue to get more and more copies of the same sheet until I kill the macro manually. Any ideas?

Thanks in advance for the help

Please Login or Register  to view this content.

**teylyn** · 05-02-2011, 09:46 PM

Hello,

you code contains a For/Next loop that runs on each sheet in the array. In each iteration, it copies the sheet FOOD CHANNEL-FORM and then renames it with the content of cell I3, which is probably always the same content, hence producing the same sheet name. And that is not allowed.

The code could be tidied up. Apart from not using the For/Next loop, there's a lot of selecting and activating that is not required.

**royUK** · 05-03-2011, 02:02 AM

You need to explain what you want the code to achieve. Currently I can't see why you need to loop through the array of sheets simply to copy one sheet. This will copy the sheet, rename it & format it.

Please Login or Register  to view this content.

**codsmith** · 05-03-2011, 09:39 AM

The reason I go through all those sheets, which are all pivot tables as you can see in the code, is to select 1 particular Market/Region (74 total different Market/Regions) that will all be displayed in the FOOD CHANNEL-FORM tab. And then have that FOOD CHANNEL - FORM tab copied and renamed to the appropriate Market/Region that is displayed in cell I3, which is always changing when the pivot tables Market/Region changes.

My eventual goal is to have it run through all 74 Market/Regions and spit out a copy for each Region/Market on a separate tab. The result would be 74 tabs (one for each Region/Market