Creating WBooks

[Guest]

Hi
From a cmdbutton on a form I am simply trying to create a new workbook and
copy a sheet (not necessarily sheet1) from original to new sheet1 (always).
What happens is that 2 wbooks are created - the first is blank, the second
with the required sheet copied. Why is this and how should my code be
amended?

The code is:

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

i would appreciate any advice. T.I.A

Geoff

 

[Ardus Petus]

The instruction:
Workbooks(cFilename).Sheets(1).Copy
creates a new workbook and copies sheets(1) into it

HTH

 

[Guest]

Hi Ardus
Understand that now.
But what happens is there is only one sheet in the new workbook and i need
my standard 3. In the sample code the 1st new wbook was standard num of
sheets but blank, the second was data copied but only 1 sheet. Sorry should
have mentioned that.

Geoff

 

[Bob Phillips]

All you need is

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


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Hi Bob
but that only creates a wbook with 1 sheet and I want to have the std 3.

Geoff

 

[Bob Phillips]

Private Sub cmdExecute_Click()
Workbooks.Add
Windows("Book1").Activate
Cells.Copy
Windows("Book2").Activate
ActiveSheet.Paste
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)