By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,827 IT Pros & Developers. It's quick & easy.

Problem Copying Sheet to New Workbook in Excel

NeoPa
Expert Mod 15k+
P: 31,489
Firstly, this is a very strange error and anyone throwing any light on this at all will be considered very clever indeed :D

OS = Win XP SP2
MS Excel 2000 SP3.
I get the error :
Path/File access error: '\VB53.tmp' (The actual filename varies)
when trying to copy a worksheet from my spreadsheet (The one with the active code in it), to a new one.
Expand|Select|Wrap|Line Numbers
  1. Call ActiveSheet.Copy
As I know that this is prone to failure I have developed some code which sometimes (though rarely) works (shtOldSheet is the starting worksheet and all variables are 'Dim'ed).
Expand|Select|Wrap|Line Numbers
  1. With shtOldSheet
  2.     Call .Select
  3.     'Handle bug in XP when sometimes the .Copy doesn't work
  4.     On Error Resume Next
  5.     Call .Copy
  6.     On Error GoTo 0
  7.     'If ThisWorkbook=ActiveWorkbook then the .Copy has failed
  8.     If ActiveWorkbook.FullName = ThisWorkbook.FullName Then
  9.         strSheetName = .Name
  10.         Call .Copy(Before:=shtOldSheet)
  11.         Call ActiveSheet.Move
  12.         ActiveSheet.Name = strSheetName
  13.     End If
  14. End With
Bizarrely, If I call a MsgBox() as the first thing after opening the main spreadsheet it will sometimes (almost always) work without error.
Believe me when I say that I've tried all sorts of things to tie down the cause or root of the problem, with no more success than has already been explained.

The PCs I've had this problem on, have today been upgraded to MS Office 2000 SP3. Since the upgrade I've only noticed the problem once, after fairly extensive testing.

(Click on the attachment to see it and read it more clearly.)
Attached Images
File Type: jpg ErrMsg.Jpg (6.9 KB, 203 views)
May 15 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Copy
  2. ActiveWorkbook.SaveAs Filename:="NewFile.xls"
  3.  
May 17 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
If you want to copy to the same workbook then ...

Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Copy After:=Sheets("Sheet1")
May 17 '07 #3

NeoPa
Expert Mod 15k+
P: 31,489
If you want to copy to the same workbook then ...

Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Copy After:=Sheets("Sheet1")
Thanks for trying Mary but I'm afraid it's a bit deeper than that :(
Examples of those are in the code and, as you say, it should all work. In most circumstances it does, but Excel throws a wobbly (see Post #1) sometimes and the command is not completed (It gets half way through and crashes with the error message as shown).
This question is more about finding what Excel is doing wrong (and maybe how to code around that) than how to code the sheet copies properly.
Don't think I'm ungrateful for the effort, I'm not, I just wanted to make a fairly complicated issue a little clearer if I could :)
May 17 '07 #4

Post your reply

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