470,575 Members | 1,853 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

Excel Macro Runs Great But Have to Close Multiple Sheets

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:

Expand|Select|Wrap|Line Numbers
  1. Sub VVStats()
  2. '
  3. ' VVStats Macro
  4. ' modified weekly VV stats excel for DP3
  6. Dim ws As Worksheet
  8.     For Each ws In Sheets
  9.         ws.Activate
  11.         Cells.Select
  12.         With Selection.Font
  13.             .Name = "Arial"
  14.             .Size = 11
  15.             .Strikethrough = False
  16.             .Superscript = False
  17.             .Subscript = False
  18.             .OutlineFont = False
  19.             .Shadow = False
  20.             .Underline = xlUnderlineStyleNone
  21.             .ThemeColor = xlThemeColorLight1
  22.             .TintAndShade = 0
  23.             .ThemeFont = xlThemeFontNone
  24.         End With
  25.         With Selection.Font
  26.             .Name = "Arial"
  27.             .Size = 8
  28.             .Strikethrough = False
  29.             .Superscript = False
  30.             .Subscript = False
  31.             .OutlineFont = False
  32.             .Shadow = False
  33.             .Underline = xlUnderlineStyleNone
  34.             .ThemeColor = xlThemeColorLight1
  35.             .TintAndShade = 0
  36.             .ThemeFont = xlThemeFontNone
  37.         End With
  38.         Rows("1:1").Select
  39.         Selection.Font.Bold = True
  40.         Rows("1:1").Select
  41.         With ActiveWindow
  42.             .SplitColumn = 0
  43.             .SplitRow = 1
  44.         End With
  46.         If Not ActiveSheet.AutoFilterMode Then
  47.             ActiveSheet.Range("A1").AutoFilter
  48.         End If
  50.         Cells.EntireColumn.AutoFit
  51.         Cells.EntireRow.AutoFit
  52.         ActiveWindow.FreezePanes = True
  55.     Next ws
  56. 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!

Aug 17 '18 #1
8 2953
5,436 Expert Mod 4TB
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:
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
Aug 17 '18 #2
I always make back ups to the back ups, heck yeah. Ok I will try that and let you know, thanks.
Aug 17 '18 #3
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!
Aug 17 '18 #4
5,436 Expert Mod 4TB
> 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.
Aug 18 '18 #5
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.

Aug 20 '18 #6
5,436 Expert Mod 4TB
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.
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
Aug 20 '18 #7

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!

Aug 20 '18 #8
5,436 Expert Mod 4TB
! two thumbs up !

Better than my day today... sometimes the instruments in the lab win the hill.
Aug 20 '18 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.