simple workbook create/copy queries
 

I had 3 questions realted to workbook copy/create:

1.I am trying to create a new new workbook and
simultaneously saving it. On using an already existing
name. I get run-time error 1004...

I also used Application.DisplayAlerts = False
but still Application.DisplayAlerts shows true to
me.

OTherwise how can i trap this 1004 error for file already
existing error. I cannot use directly 1004 ..because run-
tiem 1004 comes for lot many reasons. WHat is the error
number to trap it?

2.i am using the below code to copy multiple worksheets in
a newly created workbook
With Workbooks(fileSaveName).Worksheets
.Parent.Activate
On Error Resume Next
MsgBox "Now copying the selected Worksheets, to
the newly created workbook " & fileSaveName & "."
For i = 1 To totSht - 1
MsgBox Err.Number
If .Item(DataWorksheet1(i)) Is Nothing Then
DataWorksheet1(i).Copy After:=.Item(3)
Else
MsgBox "Error! Cannot copy the selected
sheets!"
End If
Next i
.Item("Sheet1").Delete
.Item("Sheet2").Delete
.Item("Sheet3").Delete
End With

If you notice I am using condition "If .Item(DataWorksheet1
(i)) Is Nothing Then" to enter into the loop which doesnt
make sense. If i try using "If err.number < 0 " this
fails...because it gives 0 at one time and 13 or 91 other
time. IF i simply write the copy statement then on any
error i cannot convey the user the message:
MsgBox "Error! Cannot copy the selected sheets!"

what condition can i use???

3. I am using :
DataWorksheet1(i).Copy After:=.Item(1)
to copy....i try using before or after it doenst make any
different on the way my sheets are being copied. My sheets
are being copied on left...whereas i want them to be
copied on the right..how can i achieve this
Thanks tremendously...

simple workbook create/copy queries
 

1. Why are you trying to delete (or overwrite) an existing file? What is
your application? Your users might not appreciate that.

2. Why are you attempting to display an error number (the line that is
"MsgBox Err.Number" right after the For statement), when an error has not
yet occurred? Review the information in Visual Basic Help on "On Error
Statement". (There appear to be many problems with this routine; can you
explain how many sheets the workbook has before the copying begins, and why
you need to copy worksheets?)

3. After you copy the first worksheet, remember that the index numbers of
all sheets will shift by one. So if you have a workbook with 7 worksheets in
it and you copy sheet 3 to be right after sheet 3, then the old sheet 4 will
now be sheet 5, so a copy of it will have to be sheet 6, etc.
--
Hope this helps,
Bill


"monika" wrote in message
...
I had 3 questions realted to workbook copy/create:

1.I am trying to create a new new workbook and
simultaneously saving it. On using an already existing
name. I get run-time error 1004...

I also used Application.DisplayAlerts = False
but still Application.DisplayAlerts shows true to
me.

OTherwise how can i trap this 1004 error for file already
existing error. I cannot use directly 1004 ..because run-
tiem 1004 comes for lot many reasons. WHat is the error
number to trap it?

2.i am using the below code to copy multiple worksheets in
a newly created workbook
With Workbooks(fileSaveName).Worksheets
.Parent.Activate
On Error Resume Next
MsgBox "Now copying the selected Worksheets, to
the newly created workbook " & fileSaveName & "."
For i = 1 To totSht - 1
MsgBox Err.Number
If .Item(DataWorksheet1(i)) Is Nothing Then
DataWorksheet1(i).Copy After:=.Item(3)
Else
MsgBox "Error! Cannot copy the selected
sheets!"
End If
Next i
.Item("Sheet1").Delete
.Item("Sheet2").Delete
.Item("Sheet3").Delete
End With

If you notice I am using condition "If .Item(DataWorksheet1
(i)) Is Nothing Then" to enter into the loop which doesnt
make sense. If i try using "If err.number < 0 " this
fails...because it gives 0 at one time and 13 or 91 other
time. IF i simply write the copy statement then on any
error i cannot convey the user the message:
MsgBox "Error! Cannot copy the selected sheets!"

what condition can i use???

3. I am using :
DataWorksheet1(i).Copy After:=.Item(1)
to copy....i try using before or after it doenst make any
different on the way my sheets are being copied. My sheets
are being copied on left...whereas i want them to be
copied on the right..how can i achieve this
Thanks tremendously...