473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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:

Expand|Select|Wrap|Line Numbers
  1. Sub VVStats()
  2. '
  3. ' VVStats Macro
  4. ' modified weekly VV stats excel for DP3
  5.  
  6. Dim ws As Worksheet
  7.  
  8.     For Each ws In Sheets
  9.         ws.Activate
  10.  
  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
  45.  
  46.         If Not ActiveSheet.AutoFilterMode Then
  47.             ActiveSheet.Range("A1").AutoFilter
  48.         End If
  49.  
  50.         Cells.EntireColumn.AutoFit
  51.         Cells.EntireRow.AutoFit
  52.         ActiveWindow.FreezePanes = True
  53.  
  54.  
  55.     Next ws
  56. End Sub
  57.  
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
Aug 17 '18 #1
8 3998
zmbd
5,501 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:
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
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
zmbd
5,501 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.

Interesting.
Aug 20 '18 #6
zmbd
5,501 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.
<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
Aug 20 '18 #7
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
Aug 20 '18 #8
zmbd
5,501 Expert Mod 4TB
! two thumbs up !

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

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

Similar topics

2
by: Mrs Howl | last post by:
I don't know if there's even a way to do what I want. I click on a button in an Access form, and it opens an instance of Excel, and opens a workbook and runs a macro that's in it. So far, fine,...
2
by: geronimo_me | last post by:
Hi, I have the following code in an access module: Sub Run_Excel_Macro() Dim xls, xlWB As Object Dim strFile, strMacro As String
6
by: geronimo_me | last post by:
Hi, I am trying to run an Excel macro from an Access module, however when I run the code the macro runs but then I get an error in Access. The error is: Run-time error "440", Automation error. ...
0
by: modularmix | last post by:
Does anyone know how to run the Excel Macro for two different spreadsheets in parallel. Here is the code that works sequentially. Workbooks.Open Filename:="C:\Documents and...
0
by: scolivas | last post by:
I don't know if this is the right place to ask or not...so here it goes... I have an excel spreadsheet with a macro that refreshes data which is pulled from an external data source, which happens...
7
by: NeverLift | last post by:
This is probably answered elsewhere, but I've searched the Web and VBA for Excel manual, find no answers. I have a VBA-coded macro in an Excel workbook that is to open another existing workbook --...
1
by: CF FAN | last post by:
Can Report Builder Export Excel Files with multiple Sheets How can I create a report that it can export in excel by multiple
1
by: Catbkr1 | last post by:
I have to automatically create some Excel Spreadsheets based on automatically generated .CSV files that are produced overnight. Each .CSV has several columns that need to be deleted. The same...
1
by: preciouslife73 | last post by:
I have read a few available functions similar to my concerns. And I have noticed some were not answered for a long time. My concerns is that what was provided in the forum, when a query or a table...
7
by: preciouslife73 | last post by:
'actually I have a macro that exports multiple querys or table into many excel spreadsheet with multiple worksheets. 'but this is just to test the Excel Macro to work on the exported files. 'is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.