VBA worksheet copy problem

[Lochnagar]

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]

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]

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.

 

[Lochnagar]

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