VBA worksheet copy problem

Lochnagar

New Member
Joined
Jan 28, 2008
Messages
43
hi,

I was hoping someone might be able to help shed some light on the following.

When I execute the following macro a new workbook (containing a single worksheet). However, I've noticed that when I execute the macro the name of the new workbook (containing a single worksheet) that appears is e.g. 'Book42.xls', which when I close without saving and then re-execute the macro the new workbook that appears (again, containing a single worksheet) name is 'Book43.xls'. What I'm worried about is why is the number increasing by 1 each time the macros is run - is this because somewhere on my computer are Excel files - Book42 all the way down to Book1? I've tried looking but to no avail. Any help would be greatly appreciated.

Sub Cpy()
ActiveWorkbook.Worksheets("Sheet1").Copy
End Sub

Many thanks
Lochnagar
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Lochnagar

In that code nowhere do you name the workbook, so what is VBA supposed to call it?


Unless when you save it you specify a name then VBA will give it an arbritrary name.

What that is has probably no rhyme nor reason - well none that I know of anyway.@)
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Basically, don't worry about. If you care to know what is going on, see what I ramble on about below.

I am sure that someone has explained this before. Since Excel's code is not freeware, we can only guess at what it does in the background. Here is my guess. As you create new workbooks, they are saved to your Temp folder. Excel tracks these and adds the next one based on the highest number TMP file.

To see this in action: Start > Run > %temp% > OK. This opens your Temp folder. There is also a %tmp% folder sometimes. You should see some filenames like 1331.tmp, 1332.tmp, 1333.tmp, 1334.tmp. So, if Book2 is 1334.tmp then Book3 would be 1335.tmp. You can select View > Refresh, in Explorer to see the tmp files. You can see by trial and error that Excel just adds the next book number based on the last book's name in a TMP file.

You should periodically clean house. CCleaner can help you clean temp folder files. Of course you can delete the files from that folder manually but I recommend doing it just after a boot.
 
Last edited:

Lochnagar

New Member
Joined
Jan 28, 2008
Messages
43
Hi Kenneth and Norie,

Sorry for taking so long to reply. Thanks for your replies. I checked the Temp folder but couldn't see any file that may correspond to a temp file created by excel (in terms of .xls files and or files created or modified when I run the macros). But I noticed that everytime I use the macro after restarting my computer the number that is incremented each time the macro is run begins at 2 (i.e. 'Book2.xls' assuming the file containing the macro is titled 'Book1.xls')...which suggests (to me at least) that the temp files created when I run the macros only exist for the duration of the computer being on, but I'm by no means an authority on computers (so I'm probably wrong:).

Anyway, thanks again for your help, it's very much appreciated.

Lochnagar
 

Forum statistics

Threads
1,081,748
Messages
5,361,041
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top