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
Sub REPORTGEN()
Application.ScreenUpdating = False
sheetlist = Array("FROZENDESSERTS", "SORBETGELATO", "SORBET", "GELATO", "SORBETGELATO2", "SORBET2", "GELATO2", "SUPERPREMIUM", "SINGLESERVE", "NOVELTIES", "NONDAIRYDESS", "NONDAIRYNOV")
For i = LBound(sheetlist) To UBound(sheetlist)
Worksheets(sheetlist(i)).Activate
ActiveSheet.PivotTables("PT1").PivotFields("REGION").CurrentPage = _
"TOTAL US - DRUG CHANNEL"
ActiveSheet.Next.Select
Sheets("FOOD CHANNEL-FORM").Select
Sheets("FOOD CHANNEL-FORM").Copy After:=Sheets(14)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Name = Range("I3").Value
Range("A1").Select
Cells.EntireRow.Hidden = False
For Each cell In ActiveSheet.Range("C12:C257")
If cell.Value = "na" Then cell.EntireRow.Hidden = True
Next
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Range("A1").Select
Application.ScreenUpdating = True
Next
End Sub
Bookmarks