Excel Macro Runs Great But Have to Close Multiple Sheets

[USTRAGNU1]

Good Day,

I created a macro in Excel that works great. It formats multiple sheets exactly as expected, but when it completes it seems like I have to close out four or five worksheets before I can close out of excel completely.

One item of note: I experienced errors when I first built the macro. When I went in to edit the macro, I noticed a ton of temp macros that were never there before. Could I assume this is the problem? Has anyone heard of such a problem? Would it be safe to delete all the temp macros that seem to be hung up?

Here is the code:

Sub VVStats()
'
' VVStats Macro
' modified weekly VV stats excel for DP3
 
Dim ws As Worksheet
 
    For Each ws In Sheets
        ws.Activate
 
        Cells.Select
        With Selection.Font
            .Name = "Arial"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        With Selection.Font
            .Name = "Arial"
            .Size = 8
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        Rows("1:1").Select
        Selection.Font.Bold = True
        Rows("1:1").Select
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
 
        If Not ActiveSheet.AutoFilterMode Then
            ActiveSheet.Range("A1").AutoFilter
        End If
 
        Cells.EntireColumn.AutoFit
        Cells.EntireRow.AutoFit
        ActiveWindow.FreezePanes = True
 
 
    Next ws
End Sub
 

Thank you so much for your continued support. I don't know what I would do without sites like this and my personal mentors!

UTS

[zmbd]

There's nothing in in the VBA code you've posted that should be creating any additional worksheets nor creating any additional code within the workbook.

Personally I would add:
ws.Range("B1").Select
on line 53 to bring your user back to the first cell under your header row.


As for your temporary VBA procedures (Wish MS didn't call these "Macros" in Excel):

MAKE A COPY of your workbook so if something goes in the fire you can recover. I highly recommend making backup copies during development. I usually start a new day with a new copy and work with the copy. If making any major changes to something that I already have working, I make a copy first and make the changes there - five minutes for a backup has saved me hundreds of hours of work!

Now go in and delete the "Temporary" procedures

Close and reopen (shouldn't need this step; however, it doesn't hurt)

Run your code - and see if all works as expected

[USTRAGNU1]

I always make back ups to the back ups, heck yeah. Ok I will try that and let you know, thanks.

[USTRAGNU1]

Interesting...I can't delete the .tmp items when I go into Macros. The delete button does not activate when I click on them. Oh well. Closing a couple of extra worksheets is still a lot better than what they did before I created the sub, which is open the workbook in the export email as an attachment and manually format each of seven pages! Thanks, I guess we can close this one out. I will do some more research as to why the delete button does not activate for the .tmp macros. Thanks and have a great weekend!

[zmbd]

> Question:
Are you saving to a network share folder?

These may be artifact temporary workbooks that may be being left behind for some reason and not actual VBA-Code/Procedures.

[USTRAGNU1]

The file is created from a database on a network share and added to an email. I open it from the mail attachment before I send it to click/run the macro before I send it.

However, when I perform the same process from a copy of the database on my desktop I experience the same situation.

Interesting.

[zmbd]

This could be an artifact of the database:
If it is Access, then how is it making the Excel file? If from VBA, is it creating an Excel-Instance? If it is, then is the code properly closing the Instance?

The other issue could be any add-ins to your Excel.
<Windows><R>
excel /safe
>Open the file with your VBA - enable the macro if requested.
Check to see if there are any *.tmp files showing code
Run the code
Save the file
See if there are any *.tmp files
If not then may be one of the add-ins

[USTRAGNU1]

Z,

To be honest, I did not understand some of your instructions, so I attempted to start over and delete the current PERSONAL.XLSB (I only had a couple of recorded macros).

When I got to the folder, I saw four temp files in there with the PERSONAL file, so I deleted the temp files and the problem hath been solved.

Thanks for hanging with me! You guys rock!

UTS

[zmbd]

! two thumbs up !

Better than my day today... sometimes the instruments in the lab win the hill.