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