This'll stump you! Problem with Copy Sheet between Excel versions
I would try not sharing the workbook. Based on your scenario, I don't see
any reason it would need to be shared.
With ActiveWorkbook
.KeepChangeHistory = True
.SaveAs Filename:=flName, AccessMode:=xlShared
.Close
End With
would need to be changed to not share the workbook
--
Regards,
Tom Ogilvy
"tdw" wrote in message
...
I wrote a simple macro (code listed below) to individually copy a subset
of
sheets from a workbook to create new workbooks. The orginal workbook has
13
worksheets and the user wants to copy out 8 worksheets to separate
workbooks
to distribute to others.
I test it on my machine running Excel 2003 SP1 and everything works fine.
I
test it on another machine running Excel 2000 SP3 and everything works
fine.
My wife tests it at work running Excel 2000 and everything works fine. A
colleague runs it on his machine and everything works fine. My client,
running Excel 2002 SP3, runs the macro and it fails. She can't even
manually
copy a sheet in the workbook I've sent her! She works in any other
workbook
and she can manually copy a sheet.
The VBA command I use to copy a worksheet to a new workbook, the line on
which the macro fails, is simply "Sheets(shtName).Copy". The error is
"Path/File access error (Error 75)."
So, I'm thinking there's some issue between versions. I save my workbook
in
Excel 2003 using "Microsft Excel 97- Excel 2003 & 5.0/95 Workbook" file
type.
Open it on the machine running 2000-SP3 and do a Save As using "Microsoft
Excel Workbook" thinking that it'll now be saved as a 2000 workbook. Send
it
to the client and IT WORKS! ... But Wait ... there are changes ...
I make the changes, go through the same silly Save As process listed
above,
send it back to the client and ... IT DOESN'T WORK!! What's with that?
This
is just plain rude!
If you've read this far, I Thank You for sticking with me. If you have any
suggestions as to what the issue may be or how I might solve the problem I
would be sincerely grateful!
Thanks in advance for any help that may be provided!
tdw
Private Sub CreateWorkbook(shtName As String, flName As String, asAtDt As
String)
Application.StatusBar = "Creating the " + flName + " workbook"
Sheets(shtName).Copy
'the following formatting replaces existing formulae with values so
that
the
'user is not continually prompted to update the formulae when they
open
'the workbook
ActiveSheet.Range("H1").Value = "'" + asAtDt
Cells.Find(What:="Plus Already Approved This Fiscal Year",
LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False).Cells(1, 2).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Cells.Find(What:="Target For This Fiscal Year (High End of Range)",
LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False).Cells(1, 2).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A1").Activate
With ActiveWorkbook
.KeepChangeHistory = True
.SaveAs Filename:=flName, AccessMode:=xlShared
.Close
End With
End Sub
|