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