Add New Worksheet in new workbook

J

jlclyde

I am trying to have VBA add a workbook, name sheet 1 as Quote Proposal
and remove any other sheets. Or not add them, which ever is easier.
My code keeps throwing an error,

Set CopyBook = Workbooks.Add
CopyBook.Sheet1.Name "Quote Proposal"
Quote1.Sheets("quote").Range("A1:J52").Copy _
Destination:=CopyBook.Sheet1.Range("A1")

Thanks,
Jay
 
J

Joel

Set CopyBook = Workbooks.Add
CopyBook.sheets("Sheet1").Name = "Quote Proposal"
Quote1.Sheets("quote").Range("A1:J52").Copy _
Destination:=CopyBook.Sheets("Quote Proposal").Range("A1")
 
G

Gary''s Student

Sub hfakdf()
Set CopyBook = Workbooks.Add
CopyBook.Sheets(1).Name = "Quote Proposal"
End Sub
 
D

Dave Peterson

One more:

Dim CopyWks as worksheet
set copywks = workbooks.add(1).worksheets(1)
with copywks
.name = "Quote Proposal"
quote1.sheets("quote").range("a1:J52").copy _
destination:=.range("a1")
end with

workbooks.add(1)
will add a workbook with a single sheet.
 
J

jlclyde

One more:

Dim CopyWks as worksheet
set copywks = workbooks.add(1).worksheets(1)
with copywks
   .name = "Quote Proposal"
   quote1.sheets("quote").range("a1:J52").copy _
      destination:=.range("a1")
end with

workbooks.add(1)
will add a workbook with a single sheet.
Click to expand...

I want to thank you all for the great advice. i will definitly try to
use these in the future. I really like this last one by Dave.
Thanks,
Jay
 
Top