Hello, I can't get the following code to execute correctly. It creates a new
worksheet, but not from a template as I intended.
Can anyone help?
>Sub CTMacro()
>On Error Resume Next
> Dim ExcelSheet As Object
>
>Application.DisplayAlerts = False
>Set ExcelSheet = CreateObject("Excel.Sheet")
>Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
>
> With Workbooks("Copy of ApptDis.xls")
> .Worksheets("Sheet1").Range("B2:I2").Copy
> ExcelSheet.Application.Visible = True
> With ExcelSheet.ActiveSheet.Range("B2:I2")
> .PasteSpecial xlPasteValues
>
> ExcelSheet.SaveAs "C:\CTest.xls"
> Application.DisplayAlerts = True
> End With
> End With
>End Sub
Thanks in advance for any help.
Paul

Dim ExcelSheet As Object
set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")



Paul3rd wrote:
>
> Hello, I can't get the following code to execute correctly. It creates a new
> worksheet, but not from a template as I intended.
> Can anyone help?
> >Sub CTMacro()
> >On Error Resume Next
> > Dim ExcelSheet As Object
> >
> >Application.DisplayAlerts = False
> >Set ExcelSheet = CreateObject("Excel.Sheet")
> >Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
> >
> > With Workbooks("Copy of ApptDis.xls")
> > .Worksheets("Sheet1").Range("B2:I2").Copy
> > ExcelSheet.Application.Visible = True
> > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > .PasteSpecial xlPasteValues
> >
> > ExcelSheet.SaveAs "C:\CTest.xls"
> > Application.DisplayAlerts = True
> > End With
> > End With
> >End Sub
> Thanks in advance for any help.
> Paul

--

Dave Peterson

Dave, Thanks for your reply, I changed the code to the following but no new
workbook/worksheet is created.

>Sub CTMacro()
>On Error Resume Next
> Dim ExcelSheet As Object
>
>Application.DisplayAlerts = False
>Set ExcelSheet = Sheets.Add(Type:="C:\CTest.xlt")
>
> With Workbooks("Copy of ApptDis.xls")
> .Worksheets("Sheet1").Range("B2:I2").Copy
> ExcelSheet.Application.Visible = True
> With ExcelSheet.ActiveSheet.Range("B2:I2")
> .PasteSpecial xlPasteValues>
>
> ExcelSheet.SaveAs "C:\DaveTest.xls"
> Application.DisplayAlerts = True
> End With
> End With
>End Sub

"Dave Peterson" wrote:

> Dim ExcelSheet As Object
> set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")
>
>
>
> Paul3rd wrote:
> >
> > Hello, I can't get the following code to execute correctly. It creates a new
> > worksheet, but not from a template as I intended.
> > Can anyone help?
> > >Sub CTMacro()
> > >On Error Resume Next
> > > Dim ExcelSheet As Object
> > >
> > >Application.DisplayAlerts = False
> > >Set ExcelSheet = CreateObject("Excel.Sheet")
> > >Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
> > >
> > > With Workbooks("Copy of ApptDis.xls")
> > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > ExcelSheet.Application.Visible = True
> > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > .PasteSpecial xlPasteValues
> > >
> > > ExcelSheet.SaveAs "C:\CTest.xls"
> > > Application.DisplayAlerts = True
> > > End With
> > > End With
> > >End Sub
> > Thanks in advance for any help.
> > Paul
>
> --
>
> Dave Peterson
>

Remove or comment the "on error resume next" line.

I bet that it's masking an error.

(You sure that C:\cTest.xlt actually exists?)

Paul3rd wrote:
>
> Dave, Thanks for your reply, I changed the code to the following but no new
> workbook/worksheet is created.
>
> >Sub CTMacro()
> >On Error Resume Next
> > Dim ExcelSheet As Object
> >
> >Application.DisplayAlerts = False
> >Set ExcelSheet = Sheets.Add(Type:="C:\CTest.xlt")
> >
> > With Workbooks("Copy of ApptDis.xls")
> > .Worksheets("Sheet1").Range("B2:I2").Copy
> > ExcelSheet.Application.Visible = True
> > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > .PasteSpecial xlPasteValues>
> >
> > ExcelSheet.SaveAs "C:\DaveTest.xls"
> > Application.DisplayAlerts = True
> > End With
> > End With
> >End Sub
>
> "Dave Peterson" wrote:
>
> > Dim ExcelSheet As Object
> > set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")
> >
> >
> >
> > Paul3rd wrote:
> > >
> > > Hello, I can't get the following code to execute correctly. It creates a new
> > > worksheet, but not from a template as I intended.
> > > Can anyone help?
> > > >Sub CTMacro()
> > > >On Error Resume Next
> > > > Dim ExcelSheet As Object
> > > >
> > > >Application.DisplayAlerts = False
> > > >Set ExcelSheet = CreateObject("Excel.Sheet")
> > > >Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
> > > >
> > > > With Workbooks("Copy of ApptDis.xls")
> > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > ExcelSheet.Application.Visible = True
> > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > .PasteSpecial xlPasteValues
> > > >
> > > > ExcelSheet.SaveAs "C:\CTest.xls"
> > > > Application.DisplayAlerts = True
> > > > End With
> > > > End With
> > > >End Sub
> > > Thanks in advance for any help.
> > > Paul
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Hello Dave,
You are correct, I didn't write the complete path to CTest.xlt.
I have the code working correctly now (in a new module) except for one small
thing...
The Jan5copytest.xls closes after code executes and I want to keep it open.
(I tried inserting a line Workbook.Open("C:\Jan5copytest') but it didn't work.

>Sub NewModule()
>Dim MyWB As Workbook
>
>Application.DisplayAlerts = False
>Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
> With Workbooks("Jan5copytest.xls")
> .Worksheets("Sheet1").Range("B2:I2").Copy
>
>
> MyWB.Application.Visible = False
> With MyWB.Sheets("Sheet1").Range("B2:I2")
> .PasteSpecial xlPasteValues
>
>MyWB.SaveAs ("C:\CCTest.xls")
>MyWB.Close (True)
>Workbooks.Open ("C:\Documents and Settings\Administrator\My >Documents\Excel Code Tests\Jan5copytest.xls")
>Application.DisplayAlerts = True
> End With
> End With
>
>End Sub

"Dave Peterson" wrote:

> Remove or comment the "on error resume next" line.
>
> I bet that it's masking an error.
>
> (You sure that C:\cTest.xlt actually exists?)
>
> Paul3rd wrote:
> >
> > Dave, Thanks for your reply, I changed the code to the following but no new
> > workbook/worksheet is created.
> >
> > >Sub CTMacro()
> > >On Error Resume Next
> > > Dim ExcelSheet As Object
> > >
> > >Application.DisplayAlerts = False
> > >Set ExcelSheet = Sheets.Add(Type:="C:\CTest.xlt")
> > >
> > > With Workbooks("Copy of ApptDis.xls")
> > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > ExcelSheet.Application.Visible = True
> > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > .PasteSpecial xlPasteValues>
> > >
> > > ExcelSheet.SaveAs "C:\DaveTest.xls"
> > > Application.DisplayAlerts = True
> > > End With
> > > End With
> > >End Sub
> >
> > "Dave Peterson" wrote:
> >
> > > Dim ExcelSheet As Object
> > > set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")
> > >
> > >
> > >
> > > Paul3rd wrote:
> > > >
> > > > Hello, I can't get the following code to execute correctly. It creates a new
> > > > worksheet, but not from a template as I intended.
> > > > Can anyone help?
> > > > >Sub CTMacro()
> > > > >On Error Resume Next
> > > > > Dim ExcelSheet As Object
> > > > >
> > > > >Application.DisplayAlerts = False
> > > > >Set ExcelSheet = CreateObject("Excel.Sheet")
> > > > >Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
> > > > >
> > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > ExcelSheet.Application.Visible = True
> > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > .PasteSpecial xlPasteValues
> > > > >
> > > > > ExcelSheet.SaveAs "C:\CTest.xls"
> > > > > Application.DisplayAlerts = True
> > > > > End With
> > > > > End With
> > > > >End Sub
> > > > Thanks in advance for any help.
> > > > Paul
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

I don't understand.

I thought you wanted a worksheet added to an existing workbook--not a new
workbook.

And if Jan5copytest.xls is already open (you used it in your With statement),
why are you trying to open it again.

And is there a reason you're not copying the code from the VBE and pasting it
into your message.

To me, those ">" brackets mean that the stuff you posted isn't the same as the
code you're testing.

Paul3rd wrote:
>
> Hello Dave,
> You are correct, I didn't write the complete path to CTest.xlt.
> I have the code working correctly now (in a new module) except for one small
> thing...
> The Jan5copytest.xls closes after code executes and I want to keep it open.
> (I tried inserting a line Workbook.Open("C:\Jan5copytest') but it didn't work.
>
> >Sub NewModule()
> >Dim MyWB As Workbook
> >
> >Application.DisplayAlerts = False
> >Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
> > With Workbooks("Jan5copytest.xls")
> > .Worksheets("Sheet1").Range("B2:I2").Copy
> >
> >
> > MyWB.Application.Visible = False
> > With MyWB.Sheets("Sheet1").Range("B2:I2")
> > .PasteSpecial xlPasteValues
> >
> >MyWB.SaveAs ("C:\CCTest.xls")
> >MyWB.Close (True)
> >Workbooks.Open ("C:\Documents and Settings\Administrator\My >Documents\Excel Code Tests\Jan5copytest.xls")
> >Application.DisplayAlerts = True
> > End With
> > End With
> >
> >End Sub
>
> "Dave Peterson" wrote:
>
> > Remove or comment the "on error resume next" line.
> >
> > I bet that it's masking an error.
> >
> > (You sure that C:\cTest.xlt actually exists?)
> >
> > Paul3rd wrote:
> > >
> > > Dave, Thanks for your reply, I changed the code to the following but no new
> > > workbook/worksheet is created.
> > >
> > > >Sub CTMacro()
> > > >On Error Resume Next
> > > > Dim ExcelSheet As Object
> > > >
> > > >Application.DisplayAlerts = False
> > > >Set ExcelSheet = Sheets.Add(Type:="C:\CTest.xlt")
> > > >
> > > > With Workbooks("Copy of ApptDis.xls")
> > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > ExcelSheet.Application.Visible = True
> > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > .PasteSpecial xlPasteValues>
> > > >
> > > > ExcelSheet.SaveAs "C:\DaveTest.xls"
> > > > Application.DisplayAlerts = True
> > > > End With
> > > > End With
> > > >End Sub
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Dim ExcelSheet As Object
> > > > set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")
> > > >
> > > >
> > > >
> > > > Paul3rd wrote:
> > > > >
> > > > > Hello, I can't get the following code to execute correctly. It creates a new
> > > > > worksheet, but not from a template as I intended.
> > > > > Can anyone help?
> > > > > >Sub CTMacro()
> > > > > >On Error Resume Next
> > > > > > Dim ExcelSheet As Object
> > > > > >
> > > > > >Application.DisplayAlerts = False
> > > > > >Set ExcelSheet = CreateObject("Excel.Sheet")
> > > > > >Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
> > > > > >
> > > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > > ExcelSheet.Application.Visible = True
> > > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > > .PasteSpecial xlPasteValues
> > > > > >
> > > > > > ExcelSheet.SaveAs "C:\CTest.xls"
> > > > > > Application.DisplayAlerts = True
> > > > > > End With
> > > > > > End With
> > > > > >End Sub
> > > > > Thanks in advance for any help.
> > > > > Paul
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dave, sorry for the confusion.
My original code did create a new workbook.
Now, Jan5copytest.xls is open, it has a command button that triggers the
module.
The module creates a new workbook from the template (CTest.xlt), copies a
range to it, saves the new workbook as (CCTest.xls) and then closes it.
Which is what I wanted.
The module is also closing Jan5copytest.xls which is not what I wanted.
I won't use the > character anymore.
Paul

"Dave Peterson" wrote:

> I don't understand.
>
> I thought you wanted a worksheet added to an existing workbook--not a new
> workbook.
>
> And if Jan5copytest.xls is already open (you used it in your With statement),
> why are you trying to open it again.
>
> And is there a reason you're not copying the code from the VBE and pasting it
> into your message.
>
> To me, those ">" brackets mean that the stuff you posted isn't the same as the
> code you're testing.
>
> Paul3rd wrote:
> >
> > Hello Dave,
> > You are correct, I didn't write the complete path to CTest.xlt.
> > I have the code working correctly now (in a new module) except for one small
> > thing...
> > The Jan5copytest.xls closes after code executes and I want to keep it open.
> > (I tried inserting a line Workbook.Open("C:\Jan5copytest') but it didn't work.
> >
> > >Sub NewModule()
> > >Dim MyWB As Workbook
> > >
> > >Application.DisplayAlerts = False
> > >Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
> > > With Workbooks("Jan5copytest.xls")
> > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > >
> > >
> > > MyWB.Application.Visible = False
> > > With MyWB.Sheets("Sheet1").Range("B2:I2")
> > > .PasteSpecial xlPasteValues
> > >
> > >MyWB.SaveAs ("C:\CCTest.xls")
> > >MyWB.Close (True)
> > >Workbooks.Open ("C:\Documents and Settings\Administrator\My >Documents\Excel Code Tests\Jan5copytest.xls")
> > >Application.DisplayAlerts = True
> > > End With
> > > End With
> > >
> > >End Sub
> >
> > "Dave Peterson" wrote:
> >
> > > Remove or comment the "on error resume next" line.
> > >
> > > I bet that it's masking an error.
> > >
> > > (You sure that C:\cTest.xlt actually exists?)
> > >
> > > Paul3rd wrote:
> > > >
> > > > Dave, Thanks for your reply, I changed the code to the following but no new
> > > > workbook/worksheet is created.
> > > >
> > > > >Sub CTMacro()
> > > > >On Error Resume Next
> > > > > Dim ExcelSheet As Object
> > > > >
> > > > >Application.DisplayAlerts = False
> > > > >Set ExcelSheet = Sheets.Add(Type:="C:\CTest.xlt")
> > > > >
> > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > ExcelSheet.Application.Visible = True
> > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > .PasteSpecial xlPasteValues>
> > > > >
> > > > > ExcelSheet.SaveAs "C:\DaveTest.xls"
> > > > > Application.DisplayAlerts = True
> > > > > End With
> > > > > End With
> > > > >End Sub
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Dim ExcelSheet As Object
> > > > > set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")
> > > > >
> > > > >
> > > > >
> > > > > Paul3rd wrote:
> > > > > >
> > > > > > Hello, I can't get the following code to execute correctly. It creates a new
> > > > > > worksheet, but not from a template as I intended.
> > > > > > Can anyone help?
> > > > > > >Sub CTMacro()
> > > > > > >On Error Resume Next
> > > > > > > Dim ExcelSheet As Object
> > > > > > >
> > > > > > >Application.DisplayAlerts = False
> > > > > > >Set ExcelSheet = CreateObject("Excel.Sheet")
> > > > > > >Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
> > > > > > >
> > > > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > > > ExcelSheet.Application.Visible = True
> > > > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > > > .PasteSpecial xlPasteValues
> > > > > > >
> > > > > > > ExcelSheet.SaveAs "C:\CTest.xls"
> > > > > > > Application.DisplayAlerts = True
> > > > > > > End With
> > > > > > > End With
> > > > > > >End Sub
> > > > > > Thanks in advance for any help.
> > > > > > Paul
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

How about posting your current version of the code?

Paul3rd wrote:
>
> Dave, sorry for the confusion.
> My original code did create a new workbook.
> Now, Jan5copytest.xls is open, it has a command button that triggers the
> module.
> The module creates a new workbook from the template (CTest.xlt), copies a
> range to it, saves the new workbook as (CCTest.xls) and then closes it.
> Which is what I wanted.
> The module is also closing Jan5copytest.xls which is not what I wanted.
> I won't use the > character anymore.
> Paul
>
> "Dave Peterson" wrote:
>
> > I don't understand.
> >
> > I thought you wanted a worksheet added to an existing workbook--not a new
> > workbook.
> >
> > And if Jan5copytest.xls is already open (you used it in your With statement),
> > why are you trying to open it again.
> >
> > And is there a reason you're not copying the code from the VBE and pasting it
> > into your message.
> >
> > To me, those ">" brackets mean that the stuff you posted isn't the same as the
> > code you're testing.
> >
> > Paul3rd wrote:
> > >
> > > Hello Dave,
> > > You are correct, I didn't write the complete path to CTest.xlt.
> > > I have the code working correctly now (in a new module) except for one small
> > > thing...
> > > The Jan5copytest.xls closes after code executes and I want to keep it open.
> > > (I tried inserting a line Workbook.Open("C:\Jan5copytest') but it didn't work.
> > >
> > > >Sub NewModule()
> > > >Dim MyWB As Workbook
> > > >
> > > >Application.DisplayAlerts = False
> > > >Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
> > > > With Workbooks("Jan5copytest.xls")
> > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > >
> > > >
> > > > MyWB.Application.Visible = False
> > > > With MyWB.Sheets("Sheet1").Range("B2:I2")
> > > > .PasteSpecial xlPasteValues
> > > >
> > > >MyWB.SaveAs ("C:\CCTest.xls")
> > > >MyWB.Close (True)
> > > >Workbooks.Open ("C:\Documents and Settings\Administrator\My >Documents\Excel Code Tests\Jan5copytest.xls")
> > > >Application.DisplayAlerts = True
> > > > End With
> > > > End With
> > > >
> > > >End Sub
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Remove or comment the "on error resume next" line.
> > > >
> > > > I bet that it's masking an error.
> > > >
> > > > (You sure that C:\cTest.xlt actually exists?)
> > > >
> > > > Paul3rd wrote:
> > > > >
> > > > > Dave, Thanks for your reply, I changed the code to the following but no new
> > > > > workbook/worksheet is created.
> > > > >
> > > > > >Sub CTMacro()
> > > > > >On Error Resume Next
> > > > > > Dim ExcelSheet As Object
> > > > > >
> > > > > >Application.DisplayAlerts = False
> > > > > >Set ExcelSheet = Sheets.Add(Type:="C:\CTest.xlt")
> > > > > >
> > > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > > ExcelSheet.Application.Visible = True
> > > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > > .PasteSpecial xlPasteValues>
> > > > > >
> > > > > > ExcelSheet.SaveAs "C:\DaveTest.xls"
> > > > > > Application.DisplayAlerts = True
> > > > > > End With
> > > > > > End With
> > > > > >End Sub
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Dim ExcelSheet As Object
> > > > > > set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")
> > > > > >
> > > > > >
> > > > > >
> > > > > > Paul3rd wrote:
> > > > > > >
> > > > > > > Hello, I can't get the following code to execute correctly. It creates a new
> > > > > > > worksheet, but not from a template as I intended.
> > > > > > > Can anyone help?
> > > > > > > >Sub CTMacro()
> > > > > > > >On Error Resume Next
> > > > > > > > Dim ExcelSheet As Object
> > > > > > > >
> > > > > > > >Application.DisplayAlerts = False
> > > > > > > >Set ExcelSheet = CreateObject("Excel.Sheet")
> > > > > > > >Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
> > > > > > > >
> > > > > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > > > > ExcelSheet.Application.Visible = True
> > > > > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > > > > .PasteSpecial xlPasteValues
> > > > > > > >
> > > > > > > > ExcelSheet.SaveAs "C:\CTest.xls"
> > > > > > > > Application.DisplayAlerts = True
> > > > > > > > End With
> > > > > > > > End With
> > > > > > > >End Sub
> > > > > > > Thanks in advance for any help.
> > > > > > > Paul
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

ps. What's the name of the workbook that holds this code?

Dave Peterson wrote:
>
> How about posting your current version of the code?
>
> Paul3rd wrote:
> >
> > Dave, sorry for the confusion.
> > My original code did create a new workbook.
> > Now, Jan5copytest.xls is open, it has a command button that triggers the
> > module.
> > The module creates a new workbook from the template (CTest.xlt), copies a
> > range to it, saves the new workbook as (CCTest.xls) and then closes it.
> > Which is what I wanted.
> > The module is also closing Jan5copytest.xls which is not what I wanted.
> > I won't use the > character anymore.
> > Paul
> >
> > "Dave Peterson" wrote:
> >
> > > I don't understand.
> > >
> > > I thought you wanted a worksheet added to an existing workbook--not a new
> > > workbook.
> > >
> > > And if Jan5copytest.xls is already open (you used it in your With statement),
> > > why are you trying to open it again.
> > >
> > > And is there a reason you're not copying the code from the VBE and pasting it
> > > into your message.
> > >
> > > To me, those ">" brackets mean that the stuff you posted isn't the same as the
> > > code you're testing.
> > >
> > > Paul3rd wrote:
> > > >
> > > > Hello Dave,
> > > > You are correct, I didn't write the complete path to CTest.xlt.
> > > > I have the code working correctly now (in a new module) except for one small
> > > > thing...
> > > > The Jan5copytest.xls closes after code executes and I want to keep it open.
> > > > (I tried inserting a line Workbook.Open("C:\Jan5copytest') but it didn't work.
> > > >
> > > > >Sub NewModule()
> > > > >Dim MyWB As Workbook
> > > > >
> > > > >Application.DisplayAlerts = False
> > > > >Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
> > > > > With Workbooks("Jan5copytest.xls")
> > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > >
> > > > >
> > > > > MyWB.Application.Visible = False
> > > > > With MyWB.Sheets("Sheet1").Range("B2:I2")
> > > > > .PasteSpecial xlPasteValues
> > > > >
> > > > >MyWB.SaveAs ("C:\CCTest.xls")
> > > > >MyWB.Close (True)
> > > > >Workbooks.Open ("C:\Documents and Settings\Administrator\My >Documents\Excel Code Tests\Jan5copytest.xls")
> > > > >Application.DisplayAlerts = True
> > > > > End With
> > > > > End With
> > > > >
> > > > >End Sub
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Remove or comment the "on error resume next" line.
> > > > >
> > > > > I bet that it's masking an error.
> > > > >
> > > > > (You sure that C:\cTest.xlt actually exists?)
> > > > >
> > > > > Paul3rd wrote:
> > > > > >
> > > > > > Dave, Thanks for your reply, I changed the code to the following but no new
> > > > > > workbook/worksheet is created.
> > > > > >
> > > > > > >Sub CTMacro()
> > > > > > >On Error Resume Next
> > > > > > > Dim ExcelSheet As Object
> > > > > > >
> > > > > > >Application.DisplayAlerts = False
> > > > > > >Set ExcelSheet = Sheets.Add(Type:="C:\CTest.xlt")
> > > > > > >
> > > > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > > > ExcelSheet.Application.Visible = True
> > > > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > > > .PasteSpecial xlPasteValues>
> > > > > > >
> > > > > > > ExcelSheet.SaveAs "C:\DaveTest.xls"
> > > > > > > Application.DisplayAlerts = True
> > > > > > > End With
> > > > > > > End With
> > > > > > >End Sub
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > Dim ExcelSheet As Object
> > > > > > > set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Paul3rd wrote:
> > > > > > > >
> > > > > > > > Hello, I can't get the following code to execute correctly. It creates a new
> > > > > > > > worksheet, but not from a template as I intended.
> > > > > > > > Can anyone help?
> > > > > > > > >Sub CTMacro()
> > > > > > > > >On Error Resume Next
> > > > > > > > > Dim ExcelSheet As Object
> > > > > > > > >
> > > > > > > > >Application.DisplayAlerts = False
> > > > > > > > >Set ExcelSheet = CreateObject("Excel.Sheet")
> > > > > > > > >Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
> > > > > > > > >
> > > > > > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > > > > > ExcelSheet.Application.Visible = True
> > > > > > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > > > > > .PasteSpecial xlPasteValues
> > > > > > > > >
> > > > > > > > > ExcelSheet.SaveAs "C:\CTest.xls"
> > > > > > > > > Application.DisplayAlerts = True
> > > > > > > > > End With
> > > > > > > > > End With
> > > > > > > > >End Sub
> > > > > > > > Thanks in advance for any help.
> > > > > > > > Paul
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson

The name of the workbook that holds this code is Jan5copytest.xls.
This is the current code.
Sub NewModule()
Dim MyWB As Workbook

Application.DisplayAlerts = False
Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
With Workbooks("Jan5copytest.xls")
.Worksheets("Sheet1").Range("B2:I2").Copy

MyWB.Application.Visible = False
With MyWB.Sheets("Sheet1").Range("B2:I2")
.PasteSpecial xlPasteValues

MyWB.SaveAs ("C:\CCTest.xls")
MyWB.Close (False)

Application.DisplayAlerts = True
End With
End With

End Sub

"Dave Peterson" wrote:

> ps. What's the name of the workbook that holds this code?
>
> Dave Peterson wrote:
> >
> > How about posting your current version of the code?
> >
> > Paul3rd wrote:
> > >
> > > Dave, sorry for the confusion.
> > > My original code did create a new workbook.
> > > Now, Jan5copytest.xls is open, it has a command button that triggers the
> > > module.
> > > The module creates a new workbook from the template (CTest.xlt), copies a
> > > range to it, saves the new workbook as (CCTest.xls) and then closes it.
> > > Which is what I wanted.
> > > The module is also closing Jan5copytest.xls which is not what I wanted.
> > > I won't use the > character anymore.
> > > Paul
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I don't understand.
> > > >
> > > > I thought you wanted a worksheet added to an existing workbook--not a new
> > > > workbook.
> > > >
> > > > And if Jan5copytest.xls is already open (you used it in your With statement),
> > > > why are you trying to open it again.
> > > >
> > > > And is there a reason you're not copying the code from the VBE and pasting it
> > > > into your message.
> > > >
> > > > To me, those ">" brackets mean that the stuff you posted isn't the same as the
> > > > code you're testing.
> > > >
> > > > Paul3rd wrote:
> > > > >
> > > > > Hello Dave,
> > > > > You are correct, I didn't write the complete path to CTest.xlt.
> > > > > I have the code working correctly now (in a new module) except for one small
> > > > > thing...
> > > > > The Jan5copytest.xls closes after code executes and I want to keep it open.
> > > > > (I tried inserting a line Workbook.Open("C:\Jan5copytest') but it didn't work.
> > > > >
> > > > > >Sub NewModule()
> > > > > >Dim MyWB As Workbook
> > > > > >
> > > > > >Application.DisplayAlerts = False
> > > > > >Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
> > > > > > With Workbooks("Jan5copytest.xls")
> > > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > >
> > > > > >
> > > > > > MyWB.Application.Visible = False
> > > > > > With MyWB.Sheets("Sheet1").Range("B2:I2")
> > > > > > .PasteSpecial xlPasteValues
> > > > > >
> > > > > >MyWB.SaveAs ("C:\CCTest.xls")
> > > > > >MyWB.Close (True)
> > > > > >Workbooks.Open ("C:\Documents and Settings\Administrator\My >Documents\Excel Code Tests\Jan5copytest.xls")
> > > > > >Application.DisplayAlerts = True
> > > > > > End With
> > > > > > End With
> > > > > >
> > > > > >End Sub
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Remove or comment the "on error resume next" line.
> > > > > >
> > > > > > I bet that it's masking an error.
> > > > > >
> > > > > > (You sure that C:\cTest.xlt actually exists?)
> > > > > >
> > > > > > Paul3rd wrote:
> > > > > > >
> > > > > > > Dave, Thanks for your reply, I changed the code to the following but no new
> > > > > > > workbook/worksheet is created.
> > > > > > >
> > > > > > > >Sub CTMacro()
> > > > > > > >On Error Resume Next
> > > > > > > > Dim ExcelSheet As Object
> > > > > > > >
> > > > > > > >Application.DisplayAlerts = False
> > > > > > > >Set ExcelSheet = Sheets.Add(Type:="C:\CTest.xlt")
> > > > > > > >
> > > > > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > > > > ExcelSheet.Application.Visible = True
> > > > > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > > > > .PasteSpecial xlPasteValues>
> > > > > > > >
> > > > > > > > ExcelSheet.SaveAs "C:\DaveTest.xls"
> > > > > > > > Application.DisplayAlerts = True
> > > > > > > > End With
> > > > > > > > End With
> > > > > > > >End Sub
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > Dim ExcelSheet As Object
> > > > > > > > set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Paul3rd wrote:
> > > > > > > > >
> > > > > > > > > Hello, I can't get the following code to execute correctly. It creates a new
> > > > > > > > > worksheet, but not from a template as I intended.
> > > > > > > > > Can anyone help?
> > > > > > > > > >Sub CTMacro()
> > > > > > > > > >On Error Resume Next
> > > > > > > > > > Dim ExcelSheet As Object
> > > > > > > > > >
> > > > > > > > > >Application.DisplayAlerts = False
> > > > > > > > > >Set ExcelSheet = CreateObject("Excel.Sheet")
> > > > > > > > > >Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")
> > > > > > > > > >
> > > > > > > > > > With Workbooks("Copy of ApptDis.xls")
> > > > > > > > > > .Worksheets("Sheet1").Range("B2:I2").Copy
> > > > > > > > > > ExcelSheet.Application.Visible = True
> > > > > > > > > > With ExcelSheet.ActiveSheet.Range("B2:I2")
> > > > > > > > > > .PasteSpecial xlPasteValues
> > > > > > > > > >
> > > > > > > > > > ExcelSheet.SaveAs "C:\CTest.xls"
> > > > > > > > > > Application.DisplayAlerts = True
> > > > > > > > > > End With
> > > > > > > > > > End With
> > > > > > > > > >End Sub
> > > > > > > > > Thanks in advance for any help.
> > > > > > > > > Paul
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson
>