Hi
I am still getting failure or error when trying to copy a wsheet into a
newly created wbook. The procedure is initiated from a cmdbutton on a
modeless form. Eventually it will become part of an add-in.
What am I doing wrong, can someone please assist.

T.I.A.

Geoff

This code fails because it only produces a 1 sheet wbook and my default 3.

Private Sub cmdExecute_Click()
If chkConfirm = True Then
cFilename = ThisWorkbook.Name
Workbooks(cFilename).Sheets(1).Copy
End If
End Sub

This adaptation from a recent Tom Ogilvy answer errors with 'Named argument
not found' at Destination, astrerisked.

Sub AddBooks()
Dim bkOld As Workbook, bkNew As Workbook, sh As Worksheet
Set bkOld = Workbooks("WBooks Test6.xls")
Set sh = bkOld.Worksheets(1)
Set bkNew = Workbooks.Add()
bkNew.SaveAs _
Filename:= _
"D:\Excel\AutoProcess\Test7 " & bkOld, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
sh.Copy Destination:=bkNew.Worksheets(1).Range("A1") '''''' errors here
End Sub

sh.Copy Destination:=bkNew.Worksheets(1)

HTH
--
AP

"Geoff" a écrit dans le message de news:
...
Hi
I am still getting failure or error when trying to copy a wsheet into a
newly created wbook. The procedure is initiated from a cmdbutton on a
modeless form. Eventually it will become part of an add-in.
What am I doing wrong, can someone please assist.

T.I.A.

Geoff

This code fails because it only produces a 1 sheet wbook and my default 3.

Private Sub cmdExecute_Click()
If chkConfirm = True Then
cFilename = ThisWorkbook.Name
Workbooks(cFilename).Sheets(1).Copy
End If
End Sub

This adaptation from a recent Tom Ogilvy answer errors with 'Named
argument
not found' at Destination, astrerisked.

Sub AddBooks()
Dim bkOld As Workbook, bkNew As Workbook, sh As Worksheet
Set bkOld = Workbooks("WBooks Test6.xls")
Set sh = bkOld.Worksheets(1)
Set bkNew = Workbooks.Add()
bkNew.SaveAs _
Filename:= _
"D:\Excel\AutoProcess\Test7 " & bkOld, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
sh.Copy Destination:=bkNew.Worksheets(1).Range("A1") '''''' errors
here
End Sub

Hi Ardus
As before, this does not compile

Geoff

"Ardus Petus" wrote:

sh.Copy Destination:=bkNew.Worksheets(1)

HTH
--
AP

"Geoff" a écrit dans le message de news:
...
Hi
I am still getting failure or error when trying to copy a wsheet into a
newly created wbook. The procedure is initiated from a cmdbutton on a
modeless form. Eventually it will become part of an add-in.
What am I doing wrong, can someone please assist.

T.I.A.

Geoff

This code fails because it only produces a 1 sheet wbook and my default 3.

Private Sub cmdExecute_Click()
If chkConfirm = True Then
cFilename = ThisWorkbook.Name
Workbooks(cFilename).Sheets(1).Copy
End If
End Sub

This adaptation from a recent Tom Ogilvy answer errors with 'Named
argument
not found' at Destination, astrerisked.

Sub AddBooks()
Dim bkOld As Workbook, bkNew As Workbook, sh As Worksheet
Set bkOld = Workbooks("WBooks Test6.xls")
Set sh = bkOld.Worksheets(1)
Set bkNew = Workbooks.Add()
bkNew.SaveAs _
Filename:= _
"D:\Excel\AutoProcess\Test7 " & bkOld, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
sh.Copy Destination:=bkNew.Worksheets(1).Range("A1") '''''' errors
here
End Sub

sh.UsedRange.Copy Destination:=bkNew.Worksheets(1).range("A1")

HTH
--
AP

"Geoff" a écrit dans le message de news:
...
Hi Ardus
As before, this does not compile

Geoff

"Ardus Petus" wrote:

sh.Copy Destination:=bkNew.Worksheets(1)

HTH
--
AP

"Geoff" a écrit dans le message de
news:
...
Hi
I am still getting failure or error when trying to copy a wsheet into a
newly created wbook. The procedure is initiated from a cmdbutton on a
modeless form. Eventually it will become part of an add-in.
What am I doing wrong, can someone please assist.

T.I.A.

Geoff

This code fails because it only produces a 1 sheet wbook and my default
3.

Private Sub cmdExecute_Click()
If chkConfirm = True Then
cFilename = ThisWorkbook.Name
Workbooks(cFilename).Sheets(1).Copy
End If
End Sub

This adaptation from a recent Tom Ogilvy answer errors with 'Named
argument
not found' at Destination, astrerisked.

Sub AddBooks()
Dim bkOld As Workbook, bkNew As Workbook, sh As Worksheet
Set bkOld = Workbooks("WBooks Test6.xls")
Set sh = bkOld.Worksheets(1)
Set bkNew = Workbooks.Add()
bkNew.SaveAs _
Filename:= _
"D:\Excel\AutoProcess\Test7 " & bkOld, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
sh.Copy Destination:=bkNew.Worksheets(1).Range("A1") '''''' errors
here
End Sub

Hi Ardus

That works fine. What a relief :)

Thank you.

Geoff

"Ardus Petus" wrote:

sh.UsedRange.Copy Destination:=bkNew.Worksheets(1).range("A1")

HTH
--
AP

"Geoff" a écrit dans le message de news:
...
Hi Ardus
As before, this does not compile

Geoff

"Ardus Petus" wrote:

sh.Copy Destination:=bkNew.Worksheets(1)

HTH
--
AP

"Geoff" a écrit dans le message de
news:
...
Hi
I am still getting failure or error when trying to copy a wsheet into a
newly created wbook. The procedure is initiated from a cmdbutton on a
modeless form. Eventually it will become part of an add-in.
What am I doing wrong, can someone please assist.

T.I.A.

Geoff

This code fails because it only produces a 1 sheet wbook and my default
3.

Private Sub cmdExecute_Click()
If chkConfirm = True Then
cFilename = ThisWorkbook.Name
Workbooks(cFilename).Sheets(1).Copy
End If
End Sub

This adaptation from a recent Tom Ogilvy answer errors with 'Named
argument
not found' at Destination, astrerisked.

Sub AddBooks()
Dim bkOld As Workbook, bkNew As Workbook, sh As Worksheet
Set bkOld = Workbooks("WBooks Test6.xls")
Set sh = bkOld.Worksheets(1)
Set bkNew = Workbooks.Add()
bkNew.SaveAs _
Filename:= _
"D:\Excel\AutoProcess\Test7 " & bkOld, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
sh.Copy Destination:=bkNew.Worksheets(1).Range("A1") '''''' errors
here
End Sub